DSXchange

Hi.

I have to convert date to timestamp format.

Input:
Date format: yyyy-mm-dd
SQL type: Date(10)

Output:

Timestamp format : yyyy-mm-dd-00.00.00.000000
SQL type : Timestamp ,length-26, scale-6

Example:
input: 9999-12-31
Output: 9999-12-31-00.00.00.000000

I have gone through previous posts but could not get it working. Tried different functions in transformer and modify stage. I could not get the microsecond part. the output I am able to get is 9999-12-31-00.00.00

Thanks for the help!

Change the default timestamp of your output stage to
%yyyy-%mm-%dd-hh.nn.ss.6

Thanks DSGuru..

..output timestamp has to be loaded in DB2 table as well as a file.

in Db2 table Ia m getting microseconds with timestamp_from_date function in modify stage but I am not getting microsecond part in file.

How can I change the output column format from timestamp to one you just specified.

Thanks

Don't worry about the millisecond part. The database will add it. As for the file, specify the default timestamp in the target sequential file stage.

Its been a tough path dealing with dates on different Databases.

Working with similar situation, I adapt using user defined SQL or generate SQL with appropiate Conversion function.

In a latest job (Using SQL Server), I used getdate() function to generate the system date.

In another situation I used Oracle to_date(someval, 'yyyy-mm-dd') for loading and extract.

Hope that solves the situation giving a better work-around.

thanks for all the help.

database adds the milisecond part by itself.
As suggested by DSguru, for output to the file, I changed the timestamp column definition by specifying microsecond in extended columns. Now the output file showing millisecond part too.

Thanks.!!