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
CONVERT 'MM-DD-YYYY' to YYYY-MM-DD
Moderators: chulett, rschirm, roy
CONVERT 'MM-DD-YYYY' to YYYY-MM-DD
prabakaran.v
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.chulett wrote:Assuming you really need that output format... what is your target datatype for this? String or Date? ...
prabakaran.v
I would suggest this as your transformer derivation. note that the first argument of Convert is "'"--double quote, single quote, double quote:
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,
Code: Select all
StringToDate(Convert("'", "", inputcolumn),"%mm/%dd/%yyyy")
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
James suggests that you eliminate single quotes from the source data - a good idea if they're there.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 47
- Joined: Tue Feb 23, 2010 12:16 am
- Location: CHENNAI
Re: CONVERT 'MM-DD-YYYY' to YYYY-MM-DD
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.