conversion to timestamp

arun_im4u · Post by **arun_im4u** » Tue Dec 13, 2005 9:18 am

hi,

I have a job which extracts data from one table to another table in Oracle database. There are 3 columns in the table which has a number in milliseconds. The number represents number of milliseconds from jan1 1970. So if a record was created on jan1 1970 at 12:00 am then the value in those columns would be 0.

I have to take these columns and convert it into timestamp. Is there a inbuilt function in DS to do it or any other way.

Thanks,
Arun.

kcbland · Post by **kcbland** » Tue Dec 13, 2005 9:30 am

No but you can do the math in a reusable function.

ArndW · Post by **ArndW** » Tue Dec 13, 2005 9:48 am

The DataStage internal date is an integer denoting the number of days since Dec 31st, 1967. So Day 1 is January 1, 1970. There are (60*60*24*1000) Milliseconds in a day. The mathematical conversion between your millisecond representation and the DataStage internal representation is known, and the conversion of a DataStage internal date to any external display format you wish can be done using the appropriate conversion and the OCONV() function.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 13, 2005 3:39 pm

There are a couple of SDK routines dealing with 1970-01-01-based dates.

arun_im4u · Post by **arun_im4u** » Wed Dec 14, 2005 1:47 pm

Thanks for the suggestions.