Page 2 of 2
Posted: Tue Sep 14, 2010 2:47 pm
by allavivek
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..
Posted: Tue Sep 14, 2010 3:06 pm
by kbsuryadev
what is your input ? database/file
what is the datatype.?
i just tested a job it works fine
Posted: Tue Sep 14, 2010 3:07 pm
by mhester
Change
StringToDate(input,"%mm/%dd/%yyyy")
to this
StringToDate(input,"%mm\%dd\%yyyy")
Posted: Tue Sep 14, 2010 3:09 pm
by allavivek
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...
Posted: Tue Sep 14, 2010 3:10 pm
by mhester
Please do what I suggested and it will work.
Posted: Tue Sep 14, 2010 3:15 pm
by kbsuryadev
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
Posted: Tue Sep 14, 2010 3:16 pm
by mhester
Yes -that will work also. There are many ways to work this issue.
Posted: Tue Sep 14, 2010 4:37 pm
by kris007
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.
Posted: Wed Sep 15, 2010 7:44 am
by allavivek
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...