Archives of the TeradataForum
Message Posted: Thu, 04 Jan 2007 @ 14:29:41 GMT
Vijay, I think the formatting problem with storing timestamps in a numeric field arises from missing leading zeros.
For example, 07:30:00 will be stored as 73000, and this complicates the things when you try to convert that numeric data back to timestamps.
I've tried to model your situation in a simplified form and the following chain of CASTs might provide a solution:
CREATE SET TABLE tbl_event ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT (Event_Start_Tm TIME(6) TITLE 'Event Start Tm' NOT NULL DEFAULT TIME '00:00:00.000000') insert into tbl_event ( Event_Start_Tm ) sel cast(cast(73000 as time(6)) as CHAR(8) ) Event_Start_Tm ;
73000 is just an example. You could use your numeric source field. I hope there won't be any surprises after switching from the literal in this test to a column in a table.
The first CAST reinstalls the leading zeros. The second CAST helps capturing them for further processing. It is a necessary step because if you check the data type after the first CAST you'll get the INTEGER!
sel TYPE( cast(73000 as time(6)) );
The fact that this is an integer is - shall we say - on the surprising side but there might be a rationale for it. When you work with timestamps in Teradata you often have to use an intermediate CAST to (VAR)CHAR.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|