Archives of the TeradataForum

Message Posted: Fri, 13 Feb 2004 @ 15:25:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Dheeraj,

I understand your format as the number of milliseconds as counted from the base date of Jan 1 1900, 00:00:00.000. If that is right, read on; otherwise, provide more explanation and examples.

If you want to convert that format to TIMESTAMP within Teradata you could first load the data as DECIMAL(13). Then calculate the whole number of days in each value dividing it by 24*3600*1000. Having that day you could lookup the date using the TD system calendar, i.e., the SYS_CALENDAR.CALENDAR view. DAY_OF_CALENDAR column gives you exactly the number of Julian days since Jan 1 1900. That's the lucky coincidence which should simplify your calculations a lot.

You still have the remainder left after the division. It gives you the number of milliseconds elapsed in the next day after the day you looked up above. It should not be too difficult to finish the conversion now. If you don't want to write the formulae you could perhaps use INTERVAL HOURS(2) TO SECONDS(3) or something like that?

Possible catches: check whether 24*3600*1000 is OK for all days in this period. Perhaps there were exceptions like some corrections, summer and winter time changes etc in some years. They could lead to some differences between the TD Julian days and the method you used earlier with your own conversion. The easiest way would be to take some test timestamps well spread across the whole period and convert them back and forth.

Regards,

Victor

	<Prev	Next>		<<First	<Prev	Next>	Last>>