sugesstion required to load smalldatetime datatype col value from sql server to TD using tpt script

Database
Visitor

sugesstion required to load smalldatetime datatype col value from sql server to TD using tpt script

Hi i am want to load data from sql server column that is smalldatetime datatype value like 'Mon dd yyyy HH:miAM' to teradata timestamp(6) column .i am unable to load using to_timestamp(columnname,'Mon dd yyyy HH:miAM').please help me how to load data uisng tpt script uisng load operator.

 

thanks 

Siva

2 REPLIES
Apprentice

Re: sugesstion required to load smalldatetime datatype col value from sql server to TD using tpt scr

Hi Siva,

 

The 'trick' to loading dates and times into Teradata is always to match the specific formatting string to the data. The only wrinkle to this that I've come across is dealing with the AM/PM changes

 

From your post below I think that your data is described like this:

- the short month name (so data for February would start with 'Feb')

- a space

- the 2 digit day of the month

- a space

- a 4 digit year number

- a space

- 2 digits for the hour of the day (12 hour clock)

- a colon

- 2 digits for the minute of the hour

- an AM/PM indicator

 

Assuming that is correct then your 'to_timestamp' code should be:

TO_TIMESTAMP(columnname,'mon dd yyyy HH:MIam') AS ts1

Try that and see if it does what you want.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: sugesstion required to load smalldatetime datatype col value from sql server to TD using tpt scr

LOAD operator does not support function calls in the INSERT (like TO_TIMESTAMP). Try Teradata-style CAST to apply a FORMAT.

:columnname (FORMAT 'MMMBDDBYYYYBHH:MIT')