Date conversation

srini.dw · Post by **srini.dw** » Thu Mar 27, 2008 2:14 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 2:17 am

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.

srini.dw · Post by **srini.dw** » Thu Mar 27, 2008 2:22 am

I tried the option like
StringToDate(MONTH : "/" DAY : "/" YEAR), "mmm/dd/yyyy"), but in vain

ArndW · Post by **ArndW** » Thu Mar 27, 2008 2:34 am

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")

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 2:56 am

Plus you need some token designators ("%") in the format string. For example "%mm/%dd/%yyyy".

Nripendra Chand · Post by **Nripendra Chand** » Thu Mar 27, 2008 3:40 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 5:45 am

Surely it's %mm for a numeric month?

vkhandel · Post by **vkhandel** » Thu Mar 27, 2008 6:01 am

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"

ArndW · Post by **ArndW** » Thu Mar 27, 2008 6:47 am

Actually, %MMM means that the month is in 3 digit text format (i.e. "JAN","FEB")

WoMaWil · Post by **WoMaWil** » Thu Mar 27, 2008 7:01 am

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

shepli · Post by **shepli** » Thu Mar 27, 2008 2:39 pm

I thnk the problem is that two colons have missed. Insead of

Code: Select all

MONTH : "/" DAY : "/" YEAR

it should be like this

Code: Select all

MONTH : "/" : DAY : "/" : YEAR

Hope this helps.

shepli

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 6:00 pm

Beat me to it! I'd just spotted the same thing.

srini.dw · Post by **srini.dw** » Fri Mar 28, 2008 7:49 am

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