Page 1 of 1
Date conversation
Posted: Thu Mar 27, 2008 2:14 am
by srini.dw
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
Posted: Thu Mar 27, 2008 2:17 am
by ray.wurlod
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.
Posted: Thu Mar 27, 2008 2:22 am
by srini.dw
I tried the option like
StringToDate(MONTH : "/" DAY : "/" YEAR), "mmm/dd/yyyy"), but in vain
Posted: Thu Mar 27, 2008 2:34 am
by ArndW
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")
Posted: Thu Mar 27, 2008 2:56 am
by ray.wurlod
Plus you need some token designators ("%") in the format string. For example "%mm/%dd/%yyyy".
Posted: Thu Mar 27, 2008 3:40 am
by Nripendra Chand
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.
Posted: Thu Mar 27, 2008 5:45 am
by ray.wurlod
Surely it's %mm for a numeric month?
Posted: Thu Mar 27, 2008 6:01 am
by vkhandel
srini.dw wrote:I tried the option like
StringToDate(MONTH : "/" DAY : "/" YEAR), "mmm/dd/yyyy"), but in vain
your conversion function should be like -
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"
Posted: Thu Mar 27, 2008 6:47 am
by ArndW
Actually, %MMM means that the month is in 3 digit text format (i.e. "JAN","FEB")
Posted: Thu Mar 27, 2008 7:01 am
by WoMaWil
What you are discussing is the look and format. You have to take into account, what kind of target you realy have?
- sequential file
- database (what smell, Informix, Access, Unidata, DB2, Oracle.....)
- Hashed File
- Universe
Posted: Thu Mar 27, 2008 2:39 pm
by shepli
I thnk the problem is that two colons have missed. Insead of
it should be like this
Hope this helps.
shepli
Posted: Thu Mar 27, 2008 6:00 pm
by ray.wurlod
Beat me to it! I'd just spotted the same thing.
Posted: Fri Mar 28, 2008 7:49 am
by srini.dw
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