Timestamp from date

[just4geeks]

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!

[DSguru2B]

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

[just4geeks]

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

[DSguru2B]

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.

[kommven]

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.

[just4geeks]

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.!!