Date Conversion
Moderators: chulett, rschirm, roy
Date Conversion
Hi,
I am trying to convert a date in Varchar (mm-dd-yy) to Varchar(mm-dd-yyyy). Can anybody help me on this ?
I am a fresher in Datastage.
Thanks in advance.
I am trying to convert a date in Varchar (mm-dd-yy) to Varchar(mm-dd-yyyy). Can anybody help me on this ?
I am a fresher in Datastage.
Thanks in advance.
- Jaysheel -
This can be done either with string functions or by converting to date and then back to display. In your case I would do a transform derivation of "In.ShortDate[1,6]:if In.ShortDate[7,2]<'50' then '20' ELSE '19':In.ShortDate[7,2]". This assumes that all dates are from 1951 through 2050.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: Date Conversion
you can use the following derivation in transformer......... considering the input column name as tempdate.......
tempdate[1,6]:'19':tempdate[7,2]
but the question here is how to decide about the century?? i have hardcoded that here as 19 but it can be 20 also......
tempdate[1,6]:'19':tempdate[7,2]
but the question here is how to decide about the century?? i have hardcoded that here as 19 but it can be 20 also......
I think converting to the internal date first is better. To a StringToDate() call and then later a DateToString() with appropriate formatting.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The square brackets are not part of the syntax; they indicate that the date format string is optional. Also the first "%" character in your example does not connect to anything and should therefore be removed.
The date format string describes the string being converted to date. Your example date format string does not describe the format you mentioned as the source string in your original post.
The date format string describes the string being converted to date. Your example date format string does not describe the format you mentioned as the source string in your original post.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
I think DataStage too uses a century pivot to decide the centuryArndW wrote:I think converting to the internal date first is better. To a StringToDate() call and then later a DateToString() with appropriate formatting.
by default its 30, if its less than 30 it considers it as 20YY
and if its >30 then its converted to 19YY, as Ray mentioned in his example he used 50 as a century pivot.
Please correct me if I am wrong.
Regards,
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
You can specify your year cutoff in either the date format (i.e. "%NNNNyy", page 31 of the Parallel Job book) or by specifying a new value for the $APT_DATE_CENTURY_BREAK_YEAR
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>