How to convert a string to a UK date format date field in a derived column transformation

Martin Griffiths
Martin Griffiths used Ask the Experts™
on
I need to convert a string such as "12-OCT-2009 18:23:20" to a date datatype in either 12/10/2009 or 12/10/2009 18:23:20. Thought I could use (DT_DATE) in a derived column transformation but all it seems to do is convert it to 10/12/2009 6:23:20 PM which isn't in UK Format. Anyone know how I can change it to DD/MM/YYYY HH:MM:SS instead of MM/DD/YYYY HH:MM:SS please?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
CONVERT (varchar, datecolumn, 101  )
Martin GriffithsIT Development Manager

Author

Commented:
You can't put CONVERT functions in a derived column transformation though within SSIS. Are you saying I should be doing this in a different type of transformation?
try this,

(DT_DATE)(RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" ,  @[System::StartTime]  ), 2) + "/" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime]   ), 2) + "/" + (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime]) + " 00:00:00")

Put your date column insted of @[System::StartTime]
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin GriffithsIT Development Manager

Author

Commented:
I put a data viewer after the derived column transformation and it displays correct so may be the DT_DATE thing isn't the problem but the population of the excel spreadsheet itself. Should be going in as a date field but it's displaying as a general formatted field.
DT_DATE here is just converting the provided string to the date/ datetime format. I am not realy sure what you are trying to do. What I assumed was that you are trying to import the date  from txt/csv file to db..?  
Martin GriffithsIT Development Manager

Author

Commented:
My data flow task has a flat file source (pipe delimited text file), a derived column transformation (where the dt_date was being applied), then a lookup transformation and then an excel destination. I placed a data viewer between the lookup and the excel destination and the field showed in UK date format so it must be the population of the excel file that's causing the problem. Can't see how to force the field format to be a date field.  
Just set the column type to date (particular/required format) in excel then whateva date format it appears as would be changed to the one you would set it to.

Right click on the column header in excel  -> format cells -> Numbers -> date and sset the format you want.
 
Martin GriffithsIT Development Manager

Author

Commented:
Tried that. It didn't work. Seems to disregard the format I change it too and sets it to what looks like a string format.
now that's wierd. Can you please paste the format it appears in lookup , the format it appears in excel and what you are looking to convert it to.
Martin GriffithsIT Development Manager

Author

Commented:
It looks like this format in the lookup data viewer:

21/10/2009 13:52:38

and like this in the destination excel file

10/21/2009 1:52:38 PM

I'm looking to convert it to:

21/10/2009 13:52:38

i.e. as a date field rather than text.
Chris LuttrellSenior Database Architect

Commented:
Hi Grffster,
In any database and in Excel, a date is stored as a numeric representation not in any particular format.  The visible formatting is only controled when viewed, in the database you would use something like the convert statement given you up front, in Excel you have to format the cell(s) to display in the format you want.  Again it is not how it is loaded, it is how you format it.  In Excel, Format, Cells look for the format that displays in UK format and includes time in 24 hour format.  If it is not already defined, you can do it yourself with the custom selection.
HTH,
Chris
Martin GriffithsIT Development Manager

Author

Commented:
Using an excel destination I copy a template file to a new excel file and write to that one. See attached for the template layout. The field it writes to is column L, return date. Setting this field to date format (UK) seems to have little effect.
GDReturnsTemplate.xls
Don't use excel as your destination but a csf file. The csv takes the values as they are and doesn't do anything with them. If you really need excel then you will have to make sure that your date columns are not of general format but specifically of text. I think that the general format cells are turn into whatever format excel "deems" to be proper, a date in this case and that is when it will follow automatically the local regional settings on your machine or wherever is viewed. To avoid that you need a text format.

What I did in your template file was to select the entire Return Date column and right click on it > Format Cells > in the window chose Text and save it. File is attached. If this doesn't work try with the csv solution.
GDReturnsTemplate.xls
Martin GriffithsIT Development Manager

Author

Commented:
Take your point here. Many thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial