Archives of the TeradataForum

Message Posted: Tue, 21 Mar 2006 @ 10:32:11 GMT

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

Sir,

We are having Data in flat file structure extracted thru COBOL program & timestamp using systemtime in unix that is YYYYMMDDHHMMSSMS, now we want to load the same in Teradata database as timestamp. Please advice how to cast the said field in MLoad script.

The script

     Select
     Cast(
     Cast(
     Cast(
     Substring(dttm,1,4) ||'-' || Substring(dttm,5,2) ||'-' ||
     Substring(dttm,7,2) As date format 'YYYY-MM-DD') AS char(11)) ||
     cast(Cast(Substring(dttm,9,6) As time) as char(8))
     As timestamp(0))
     >From LH01_AC001000_TRIAL_BALANCE

Works file in Queryman however if I am using same in Mload

     ,DTTM = Cast(Cast(Cast(Substring(:dttm from 1
     for 4) ||'-' || Substring(:dttm from 5 for 2) ||'-' || Substring(:dttm
     from 7 for 2) As date format 'YYYY-MM-DD') AS char(11)) ||
     cast(Cast(Substring(:dttm from 9 for 6) As time) as char(8))As
     timestamp(0))

it gives error code 5407 Ansi date format error.

Is there any other way to cast timestamp

Regards,

Rajesh Tambe

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