Date conversation
Moderators: chulett, rschirm, roy
Date conversation
Hi,
My Source column all are Integer
Target column is Date (format)
My incoming values are like MONTH, DAY, YEAR. (3 different inputs)
Iam giving like MONTH : "/" DAY : "/" YEAR
I want the output as 12/31/2008, its giving error
Any ideas, please
Thanks
My Source column all are Integer
Target column is Date (format)
My incoming values are like MONTH, DAY, YEAR. (3 different inputs)
Iam giving like MONTH : "/" DAY : "/" YEAR
I want the output as 12/31/2008, its giving error
Any ideas, please
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You are generating a string, not a date. You need to apply a StringToDate() conversion - with appropriate format string - to the result that your concatenation expression generates.
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.
the format string has to match exactly, if you are doing an implicit conversion of month 2 to a string you get '2', which isn't the format '02' which the conversion requires. I don't thnk that the builtin conversions put leading zeroes in and don't have the docs handy to check. But do something like:
Code: Select all
StringToDate(Right('00:MONTH,2) : "/" : right('00':DAY,2) : "/" : Right('0000':YEAR,4), "%mm/%dd/%yyyy")
Last edited by ArndW on Thu Mar 27, 2008 4:48 am, edited 1 time in total.
<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:
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
try the following expression:
StringToDate(InputColumn, "%mmm/%dd/%yyyy")
the output of this function will be in same format which is defined at your job level or project level. Just check the date format in job properties. you can define the format there to get the date in your desired format.
StringToDate(InputColumn, "%mmm/%dd/%yyyy")
the output of this function will be in same format which is defined at your job level or project level. Just check the date format in job properties. you can define the format there to get the date in your desired format.
-Nripendra Chand
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
your conversion function should be like -srini.dw wrote:I tried the option like
StringToDate(MONTH : "/" DAY : "/" YEAR), "mmm/dd/yyyy"), but in vain
StringToDate(MONTH : "/" : DAY : "/" : YEAR), "mmm/dd/yyyy") ...
also be cautious with the value of variable MONTH. sine you want it in 3 digits (as intepreted by format specifier "mmm"), it should always have values like "001", "012" and not just only "1" or "12"
Actually, %MMM means that the month is in 3 digit text format (i.e. "JAN","FEB")
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I thnk the problem is that two colons have missed. Insead of
it should be like this
Hope this helps.
shepli
Code: Select all
MONTH : "/" DAY : "/" YEAR
Code: Select all
MONTH : "/" : DAY : "/" : YEAR
shepli
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
sorry guys for coming late,
StringToDate(Right('00':MONTH,2) : "/" : right('00':DAY,2) : "/" : Right('0000':YEAR,4), "%mm/%dd/%yyyy")
Thank you, all you guys for your support, when i execute the above statmenet it gives like 2008-02-19.
Again later i changed that by using stage variable (In.DateCol)like
In.DateCol[6,2] : "/" : In.DateCol[9,2] : "/" : In.DateCol[1,4]
StageVar[6,2] : "/" : StageVar[9,2] : "/" : StageVar[1,4]
Thank you
StringToDate(Right('00':MONTH,2) : "/" : right('00':DAY,2) : "/" : Right('0000':YEAR,4), "%mm/%dd/%yyyy")
Thank you, all you guys for your support, when i execute the above statmenet it gives like 2008-02-19.
Again later i changed that by using stage variable (In.DateCol)like
In.DateCol[6,2] : "/" : In.DateCol[9,2] : "/" : In.DateCol[1,4]
StageVar[6,2] : "/" : StageVar[9,2] : "/" : StageVar[1,4]
Thank you