1

I have a flat file that I intend to import into a table via SSIS. The file has a field with dates in the format "d/mm/yyyy". These dates eventually get stored into the Database as "yyyy/mm/dd". I know this because I run a datepart sql query on the table data to find out. I have no problem with how they are stored as I can format them via presentation. The problem is that for some of the dates, the days are swapped with the month values. ie "3/05/1989" should be stored as "1989/05/03", but it can end up as "1989/03/05" and as such, the data presentation is inconsistent with what is in the CSV files. I have searched everywhere possible for a solution and trust me I can put the links here if you want. I tried exporting from one csv file to another csv file, that one saved "d/mm/yyyy" the same way "d/mm/yyyy". My last attempt was this Date format issue while importing from a flat file to an SQL database And as you can see, it hasn't still been marked as an answer. Can anyone help me out here?

Community
  • 1
  • 1
maaizle
  • 135
  • 1
  • 11
  • Use `Substring` to extract date ,month and year in Derived transformation – praveen Jan 23 '14 at 13:34
  • Thanks praveen, but as you can see I am a bit of a noob here. Can you point me in the right direction with a small example? – maaizle Jan 23 '14 at 14:51
  • You need to start with two things: 1. Open your CSV in a TEXT editor (not excel) and observe what the data REALLY looks like. 2. Post the data type that you are loading the dates into - is it `DATE`, `DATETIME` or `VARCHAR`? – Nick.Mc Jan 24 '14 at 04:58

3 Answers3

1

Ok so after searching around this is what worked for me: 1. I used a Derived column transformation to transform the date field and split the date string by using the solution here: SSIS How to get part of a string by separator . Am surprised it hasnt been marked as an answer.

  1. Since I knew my db will switch the day and month regardless, I switched them myself and passed the result back to the same field in the Derived column transformation like so: TOKEN(FlightDate,"/",2) + "/" + TOKEN(FlightDate,"/",1) + "/" + TOKEN(FlightDate,"/",3) where 2 is the month, 1 is the day and 3 is the year.

Now the results are being saved in the correct manner and so the presentation matches the source. I know there should be a better way to do this but this works for me and I hope it works for anyone else who finds themselves in my situation

Community
  • 1
  • 1
maaizle
  • 135
  • 1
  • 11
0

Can you tell the structure of the Destination table?There should not be any problem while converting the d/mm/yyyy formats into yyyy/mm/dd.Let me know the datatype and then i can help you in further resolution of the problem.

Ayush Khanduri
  • 50
  • 8
  • The datatype is date, and for the structure of the table it doesnt realy matter as any table with a date field produces the same results here. – maaizle Jan 23 '14 at 15:02
0

If you have date in this format d/MM/yyyy then you can use the below expression to convert string to date 

(DT_DBTIMESTAMP)(Right([DATE],4) + "/" + 
SUBSTRING([DATE],FINDSTRING( [DATE], "/", 1) +1 , FINDSTRING( [DATE], "/", 2) -1)
+ "/" + substring( [DATE],1,FINDSTRING( [DATE], "/", 1) -1 ) )
praveen
  • 12,083
  • 1
  • 41
  • 49