Archives of the TeradataForum

Message Posted: Thu, 04 Jan 2007 @ 14:29:41 GMT


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

Subj:		Re: Casting integer as Time

From:		Victor Sokovin

The integer column contains just the time as we read from digital clocks. For eg, if the time is 1:05 PM , it holds 130500. And 9:58 PM would be 215800.

I have pasted the DDL of target table below. Im trying to insert into the column 'Event_Start_Tm' column (now changed to Time(6), Earlier it was defined as Time (0) ) from the source table column declared as integer. But with no avail!

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.

Regards,

Victor


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

Attachments

Library

Quick Reference