date conversion

suja.somu · Post by **suja.somu** » Thu Feb 21, 2013 10:01 am

How to convert a date format mm/dd/yyyy which is coming from csv file to yyyy-mm-dd to be sent in fixed width flat file.

Source data changed to target
6/1/2012 to be 2012-06-01
12/1/2012 to be 2012-12-01
6/25/2012 to be 2012-06-25
12/12/2012 to be 2012-12-12

How to do this conversion in datastage?

jwiles · Post by **jwiles** » Thu Feb 21, 2013 11:38 am

What have you tried and what level of success have you met with?

Look into the Type Conversion functions available in the transformer stage, or the Type Defaults options (or Column-level options) in the sequential file stage.

Regards,

jerome_rajan · Post by **jerome_rajan** » Thu Feb 21, 2013 11:55 pm

Code: Select all

DateToString(StringToDate(col,"%mm/%dd/%yyyy"),"%yyyy-%mm-%dd")

or just use

Code: Select all

StringToDate(col,"%mm/%dd/%yyyy")

assuming your datastage default format is yyyy-mm-dd

boxtoby · Post by **boxtoby** » Fri Feb 22, 2013 6:56 am

Hi Jerome,

I think you'll find your problem is that the month and day values are not consistent. ie. you have 1 and 15 and it should be 01 and 15 etc.

I use stage variables to fix this problem like this:

Day value
if len(field(DSLink21.Start_Date, "/", 1)) = 1 then "0":field(DSLink21.Start_Date, "/", 1) else field(DSLink21.Start_Date, "/", 1)

Month value
if len(field(DSLink21.Start_Date, "/", 2)) = 1 then "0":field(DSLink21.Start_Date, "/", 2) else field(DSLink21.Start_Date, "/", 2)

Year value
field(DSLink21.Start_Date, "/", 3)

Now concatenate them together:

svStartDateYear:"-":svStartDateMonth:"-":svStartDateDay

This string will go straight in to a date field without StringToDate()

Hope that helps!

Bob.

chulett · Post by **chulett** » Fri Feb 22, 2013 8:04 am

Just an FYI - there are no "date" datatypes here, just two string fields that hold two different external representations of a date. And you don't need to check the length of the fields, simply left pad the month or day values to a size of 2 using a 0 as the pad character.

jwiles · Post by **jwiles** » Fri Feb 22, 2013 9:39 am

And there's really no need pad at all. The Date Format Strings fully support variable width month and day values. %m and %d instead of %mm and %dd.

Regards,

chulett · Post by **chulett** » Fri Feb 22, 2013 10:12 am

Again, there's no dates just two strings which is why I was suggesting the substring/pad approach rather than a "there and back again" conversion to datesville.

chulett · Post by **chulett** » Fri Feb 22, 2013 10:16 am

jwiles wrote:And there's really no need pad at all. The Date Format Strings fully support variable width month and day values. %m and %d instead of %mm and %dd.

In the grand scheme of All Things DataStage, wasn't this a relatively recent change? I'm fairly certain they did not support variable month / day values back in the day... which is why they added that 'z' option or some such.

jwiles · Post by **jwiles** » Mon Feb 25, 2013 9:44 am

In the grand scheme of All Things DataStage, wasn't this a relatively recent change?

v8.0, I believe, so 5-6 years. I used the padding method plenty of times in 7.0/7.5 development and fondly bid it farewell when not necessary. Substring's good if all the OP needs to do is reformat the date string, which seems to be the case...no argument with that option.

Transformer looping's a much more recent change, but mentioning it doesn't seem to invite this chastisement?

james

chulett · Post by **chulett** » Mon Feb 25, 2013 2:21 pm

Chastisement? Really?

Sorry, just being my normal anal self.

jwiles · Post by **jwiles** » Tue Feb 26, 2013 1:01 am

Poor choice of words on my part...sorry.

Regards,

ray.wurlod · Post by **ray.wurlod** » Tue Feb 26, 2013 3:03 pm

Can we sell you a Business Glossary ?