Archives of the TeradataForum

Message Posted: Fri, 05 Jan 2007 @ 09:58:29 GMT

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

I have to add that the above was tested only with the ODBC driver for which the Date/Time Format was set to 'III'. Even then one CAST was not enough.

As other posters already verified the first CAST does not work in BTEQ. Without the ODBC help (or cheat, or whatever your attitude towards it is) we have to take care about the leading zeros and colons ourselves. FORMAT is then pretty much the only reasonably easy option. The following seems to work for me:

     insert into tbl_event ( Event_Start_Tm )
     sel substring(73000  (format '999999') (char(6)) from 1 for 2) || ':' ||
     substring(73000  (format '999999') (char(6)) from 3 for 2) || ':' ||
     substring(73000  (format '999999') (char(6)) from 5 for 2)
     Event_Start_Tm

I hope this answers the question in the original posting. Maybe just a recommendation to go with it: try to avoid such conversions in your database. Either go for Teradata timestamps or store the data in one fixed format. This thread shows once again how much confusion the subject may cause.

Regards,

Victor

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