CONVERT 'MM-DD-YYYY' to YYYY-MM-DD

[praburaj]

My source file having one date column. It has value like Ex: '02/03/2001'. I need to load my date column like yyyy-mm-dd. I don't know how to remove the single code from the date value. Plz help me to achieve this Issue?

Input :'01/02/2001'
Output:2001-02-01

[ray.wurlod]

You need a combination of StringToDate() and DateToString() functions each with an appropriate format string.

[chulett]

Assuming you really need that output format... what is your target datatype for this? String or Date?

[praburaj]

Assuming you really need that output format... what is your target datatype for this? String or Date? ...

Thanks for your response chulett. My Target data type is date. I tried even convert function to remove first single quote from the file like this. Convert ('''','',inputcolumn). But no luck.

[jwiles]

I would suggest this as your transformer derivation. note that the first argument of Convert is "'"--double quote, single quote, double quote:

Code: Select all

StringToDate(Convert("'", "", inputcolumn),"%mm/%dd/%yyyy")

Or when you import the column from your source, you can specify that the column is a quoted varchar or char, with single quotes. This would remove the quotes for you when imported.

Regards,

[ray.wurlod]

Date data types don't have a format, so all you need to do (assuming the source is a text file) is StringToDate() function.

James suggests that you eliminate single quotes from the source data - a good idea if they're there.

[manoj_23sakthi]

Hi
You may extract the source file by using sequential file??
If you using the sequential file we have Format Tab in sequential file
date/ Format string we can occur this .... or else better go by using transformer Below mentioned replies

[synsog]

For removing quotes, while reading from source file itself, for the column, in column properties, give quote as 'single'. then the sequential file will read the date column without the single quote.