Issue with Loading Date

velagapudi_k · Post by **velagapudi_k** » Mon Jan 07, 2013 4:41 pm

I have this situation. Source is a csv file. I am reading everything as varchar and trying to load to Teradata. There is a column which has dates, from the source. In the transfomer I am doing a StringToDate(IP,'yyyy-mm-dd'). Issue is for 01/01/01 it is loading as 1901-01-01 instead of 2001-01-01. Is there any way other than padding the string and then converting to date. Any thoughts are appreciated.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 07, 2013 7:52 pm

Read up about format strings and about century pivot.

You can, in fact, combine the two. For example, a format string of "2000yy-mm-dd" will force the century part of the year to be "20".

Where you only have two digit years you will definitely need to specify the century pivot year to something other than its default (1930).

chandra.shekhar@tcs.com · Tue Jan 08, 2013 12:10 am

As told by Ray, either use $APT_DATE_CENTURY_BREAK_YEAR variable in your job parameters or using the below code in transformer while doing conversion
StringToDate(<date>, "%2000yy-%mm-%dd")

velagapudi_k · Post by **velagapudi_k** » Thu Jan 10, 2013 12:22 pm

Thanks for the input folks. $APT_DATE_CENTURY_BREAK_YEAR did the trick. Guess its added in 8.7

ray.wurlod · Post by **ray.wurlod** » Thu Jan 10, 2013 2:15 pm

No, it's been there since at least version 7.5.