DSXchange

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?

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,

or just use assuming your datastage default format is yyyy-mm-dd

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.

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.

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,

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.

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.

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

Chastisement? Really?

Sorry, just being my normal anal self.

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

Regards,

Can we sell you a Business Glossary ?