Date conversions in Parallel job

[ETLHMC]

Hello..

We are using Datastage 9.1 on Windows. I am trying to convert a date format of mm/dd/yy to yyyy/mm/dd and am having problems getting the correct 4-digit year. Here is my derivation:

DateToString(StringToDate(DSLink6.DischargeDate,"%dd/%mm/%yy"),"%yyyy-%mm-%dd")

Example: 04/01/14 returns 1914-04-01

Any help you can provide would be greatly appreciated.

Thanks. :D

[chulett]

Sorry, no time to post specifics right at this point but you have a "century pivot" issue where it is using 1900 rather than 2000 for the century when going from YY to YYYY. An exact search for that phrase here should help.

[ssnegi]

You can convert the format YY to YYYY at the time of extraction by using to_char database function...If all the dates are from 2000 you can concatenate '20':YY in the string:

Code: Select all

field(DSLink6.DischargeDate,'/',1):'/':field(DSLink6.DischargeDate,'/',2):'/20':field(DSLink6.DischargeDate,'/',3)

[chulett]

Heavy emphasis on the if all dates are from 2000 part. Or you can use the year_cutoff option to handle it properly. For example:

Code: Select all

DateToString(StringToDate(DSLink6.DischargeDate,"%dd/%mm/%1930yy"),"%yyyy-%mm-%dd")

From the documentation:

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.

Make it whatever is appropriate for your data and situation. You are seeing the default of 1900 in action. If all of your years will always be in 2000 then use 2000 as the year cutoff.