String To Timestamp conversion

[srai]

Hi Team,

We have a job to laod data from Taradata table to table.
We are facing problem with the timestamp column.

Our source table is having all the columns as varchar even the date/timestamp column.

Data in source is like '17-APR-10' and we have to load in format
"2010-04-17 00:00:00".

We were tring to convert in transformer using StringToTimestamp function as StringToTimestamp(LNK_SRC.CREATED_DATE,'%YYYY-%MM-%DD %hh:%nn:%ss) but geting error as invalid Month expectin an integer

I tried to serach in forum but could not get format to covert string like
'17-APR-10' .

Any help on this would be great help.

Thanks
Shivanand Rai

[braj]

Use:
StringToTimestamp(<input Column Name>,"%dd-%mmm-%yy")

This will take input as '17-APR-10' and output as 1910-04-17 00:00:00

Thanks
Brajesh.

[srai]

Use:
StringToTimestamp(<input Column Name>,"%dd-%mmm-%yy")

This will take input as '17-APR-10' and output as 1910-04-17 00:00:00

Thanks
Brajesh.

But We need output as "2010-04-17 00:00:00' not "1910-04-17 00:00:00"

[jwiles]

Data in source is like '17-APR-10' and we have to load in format
"2010-04-17 00:00:00".

I tried to serach in forum but could not get format to covert string like
'17-APR-10' .

Shivinand,

The beauty of the format string for StringToDate() is that you can tailor it to fit just about any format. In this case, the straightforward format would probably be "%dd-%mmm-%yy".

The format string options are documented in the Parallel Job Developer's Guide.

Hope this helps!

[braj]

Use:
StringToTimestamp(<input Column Name>,"%dd-%mmm-%yy")

This will take input as '17-APR-10' and output as 1910-04-17 00:00:00

Thanks
Brajesh.

But We need output as "2010-04-17 00:00:00' not "1910-04-17 00:00:00"

Yes, missed that part.

In this case you have to use Cut-off Year. Hence put an year as cut-off year, like this
StringToTimestamp(<input Column Name>,"%dd-%mmm-%1980yy")

This will take century from 1980 till 2079 and two digits will be converted to four digits.

Information about Cut-off Year from Documents:
The year_cutoff is the year defining the beginning of the century in which all two-digit years fall. By default, the year cutoff is 1900; therefore, a two-digit year of 97 represents 1997.

Hope this helps.

Thanks
Brajesh.

[chulett]

We were tring to convert in transformer using StringToTimestamp function as StringToTimestamp(LNK_SRC.CREATED_DATE,'%YYYY-%MM-%DD %hh:%nn:%ss) but geting error as invalid Month expectin an integer

Make sure you understand that the format mask you use in that function describes the incoming string's format, not anything about the desired output - it already knows you want a "timestamp".

And I have no way to test this but the suggestion that a year of "10" in a "yy" format will translate to "1910" is incorrect. That depends on the "century pivot" value which is typically set to 30 from what I recall. Meaning, anything less than 30 will be in the current century, greater or equal than that in the previous one.

Argh... looked closer at the previous post and check what docs that I have stashed away. Maybe I'm thinking more Server than Parallel but the docs that I have use "30" as the example, so a small vindication.

The year_cutoff is the year defining the beginning of the century in which all two-digit years fall. By default, the year cutoff is 1900; therefore, a two-digit year of 97 represents 1997.

You can specify any four-digit year as the year cutoff. All two-digit years then specify the next possible year ending in the specified two digits that is the same or greater than the cutoff. For example, if you set the year cutoff to 1930, the two-digit year 30 corresponds to 1930, and the two-digit year 29 corresponds to 2029.

[Rajneesh1990]

Try this :-

StringToTimestamp(<input Column Name>,"%d-%mmm-%2000yy")

You will get Date as '2010-04-17 00:00:00'. I Tried it in my Project.

Thanks,
Rajneesh.