ADDDAYS function throws error when updating a TIMESTAMP column if "To Occurrence" parameter is used

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are trying to mask a date column for an Oracle database table using the ADDDAYS function, but when we set the "to Occurrence" parameter to 1( ), to only update the date portion. When including the "To Occurrence" parameter, we get the following error:

[pool-4-thread-1] INFO - java.lang.RuntimeException: java.lang.ClassCastException: class java.lang.String cannot be cast to class java.sql.Timestamp (java.lang.String is in module java.base of loader 'bootstrap'; java.sql.Timestamp is in module java.sql of loader 'platform')
>    at com.grid_tools.products.datamasker.masking.dbmasking.SQLUpdate.run(SQLUpdate.java:5478)
>    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>    at java.base/java.lang.Thread.run(Unknown Source)
>Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast to class java.sql.Timestamp (java.lang.String is in module java.base of loader 'bootstrap'; java.sql.Timestamp is in module java.sql of loader 'platform')
>    at com.grid_tools.products.datamasker.resolver.ResolverFunction.resolveFunction(ResolverFunction.java:2890)
>    at com.grid_tools.products.datamasker.resolver.ResolverFunction.resolveFunction(ResolverFunction.java:228)
>    at com.grid_tools.products.datamasker.masking.dbmasking.SQLUpdate.batchPreparedMasking(SQLUpdate.java:4177)
>    at com.grid_tools.products.datamasker.masking.dbmasking.SQLUpdate.run(SQLUpdate.java:5469)
>    ... 3 more 

Environment

FDM 4.10.x and 4.11.x

Cause

The ADDDAYS function is working as expected. The error is thrown because of the data type used for the column is TIMESTAMP. Because the data type is timestamp, the database is expecting the format of the update to be a timestamp format. This is why we cannot use the "From Occurrence" or "To Occurrence" parameters when updating columns using the timestamp data type. 

Resolution

To run the masking, there are two options:

1. Run the masking job, without the use of "From Occurrence" or "To Occurrence" parameters. The masking job will complete, but you may notice the time portion of the field updated slightly. For example, when masking an Oracle timestamp column, using ADDAYS = 10, the date portion of the timestamp was updated as expected, 10 days added to the original date, but the time portion of the field was also changed. The nanoseconds were updated to the nearest milliseconds.
2. If the time portion is important, and you need to retain the original time, the only option is to change the data type to VARCHAR. Then you can use the "To Occurrence" and just update the date portion for the column.