Converting string to date!!

allavivek · Post by **allavivek** » Tue Sep 14, 2010 2:47 pm

kbsuryadev wrote:sorry i forgot these % in the code

try this in the derivation

StringToDate(input,"%mm/%dd/%yyyy")

and do the same in the output as i mentioned in the other reply

i tried but same error..

kbsuryadev · Post by **kbsuryadev** » Tue Sep 14, 2010 3:06 pm

what is your input ? database/file

what is the datatype.?

i just tested a job it works fine

mhester · Post by **mhester** » Tue Sep 14, 2010 3:07 pm

Change

StringToDate(input,"%mm/%dd/%yyyy")

to this

StringToDate(input,"%mm\%dd\%yyyy")

allavivek · Post by **allavivek** » Tue Sep 14, 2010 3:09 pm

kbsuryadev wrote:what is your input ? database/file

what is the datatype.?

i just tested a job it works fine

its a file ..

actually iam doing column import of data coming from flat file...

Next in transformer iam trying to convert to specific date format...

is any thing i should do in column import stage for that column...

mhester · Post by **mhester** » Tue Sep 14, 2010 3:10 pm

Please do what I suggested and it will work.

kbsuryadev · Post by **kbsuryadev** » Tue Sep 14, 2010 3:15 pm

I just did a test it works fine

i used this data

input- 03/13/2010 .. output comes as 2010/03/13
use input datatype as (varchar) -10

in the transformer use this code

StringToDate(DSLink3.DATE,"%mm/%dd/%yyyy")

Output datatype ( DATE)

go to columns tab (output stage) and edit column meta data properties window

and under parallel properties

chose format string=%yyyy/%mm/%dd

mhester · Post by **mhester** » Tue Sep 14, 2010 3:16 pm

Yes -that will work also. There are many ways to work this issue.

kris007 · Post by **kris007** » Tue Sep 14, 2010 4:37 pm

Try to capture all the distinct values from the source by using a Remove Duplicates stage and try scanning them through to see if there are any data quality issues. We had a similar issue where business users started manually entering dates into the field and used their own formats such as 2010/03/13 (notice the slashes) or interchanged month and date positions within the date. The issue definitely looks like a data quality issue at source.

allavivek · Post by **allavivek** » Wed Sep 15, 2010 7:44 am

kbsuryadev wrote:I just did a test it works fine

i used this data

input- 03/13/2010 .. output comes as 2010/03/13
use input datatype as (varchar) -10

in the transformer use this code

StringToDate(DSLink3.DATE,"%mm/%dd/%yyyy")

Output datatype ( DATE)

go to columns tab (output stage) and edit column meta data properties window

and under parallel properties

chose format string=%yyyy/%mm/%dd

It worked

Thanks for all suggestions...

Iam marking this thread resolved...