date conversion
Moderators: chulett, rschirm, roy
date conversion
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?
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Code: Select all
DateToString(StringToDate(col,"%mm/%dd/%yyyy"),"%yyyy-%mm-%dd")
Code: Select all
StringToDate(col,"%mm/%dd/%yyyy")
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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.
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.
Bob Oxtoby
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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,
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
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 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.In the grand scheme of All Things DataStage, wasn't this a relatively recent change?
Transformer looping's a much more recent change, but mentioning it doesn't seem to invite this chastisement?
james
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: