I have a file with date like this in it: 1/16/1983
But need to change to UK format. How is this best done in DS?
I’ve got a simple ETL job loading data from file to a staging tabe then to a final table. In the staging I have the dates fields set as varchar then I want to convert or change it in the job to 16/01/1983 if possible?
It seems like TO_DATE(column1, ‘DD/MM/YYYY’) isn’t doing anything with the actual format just the numbers, it doesn’t know the 1 is actually a month not a day so it just lets it through the net. I understand this is ok for if I wanted to change from 01-16-1983 to 01/16/1983 but not change day and month around.
I’ve also now tried changing field data type to datetime in the staging table, and the to_date(column.field, ‘dd/mm/yyyy’) for this field isn’t working, the date is showing as ‘1983-01-16 00:00:00.0000000’. Does the final field data type need to be varchar to then allow it to be converted to the DD/MM/YYYY format?
Thanks
You need to do one of two things. The first makes the most sense to me, but YMMV.
Either:
Change the date format in your target database to DD/MM/YYYY. How you do this depends entirely upon which database it is.
Store the dates as varchar. In that case you use to_char(column1, ‘DD/MM/YYYY’) to get what you want.
Just to be clear, storing dates as varchar will likely cause you MANY problems in the long run. You are much better off just storing as dates and let whatever is displaying the dates handle the formatting for output to users.
Following Ernie ideas, and over all, the final tip, I would change the field in the flat file definition to date with the right input format.
Then, if you propagate that field inside DS, it should take all the measures to maintain the date end-to-end.
The way you “see” that date in the DB depends on the presentation layer and the connection, regional settings and custom formats, but inside the DB is always a DATE.
(BOB member since 2005-09-16)
(BOB member since 2006-02-02)