0

I am trying to insert a date value in the format of mm\dd\yyyy to SQL Server. In my source .csv file its in the format dd\mm\YYYY. Is it possible to insert a datefield to SQL Server in this format?

Thanks...

César
  • 9,939
  • 6
  • 53
  • 74
Sinoy Devassy
  • 564
  • 4
  • 11
  • 27
  • 1
    You don't insert date value into a DB in any particular string format. Your issue is turning string value stored in CSV file into a DateTime instance, which has nothing to do with sql server. I am not sure how to do that in SSIS. – Ilia G Nov 23 '11 at 14:00
  • In ssis we can change the format to mm\dd\yyyy by using the substring function ,etc...but when we trying to insert it into the database it will taken as yyyy-mm-dd.. – Sinoy Devassy Nov 23 '11 at 14:04
  • @siva...can we change the YYYY-MM-DD hh:mi:ss.ms of sql to mm-dd-yyyy? or we can only change the view of date in other formats? – Sinoy Devassy Nov 23 '11 at 14:30
  • Check out the fast parse option http://stackoverflow.com/questions/8186291/import-string-date-in-derived-column – billinkc Nov 23 '11 at 15:48
  • 3
    @Siva date isn't stored in sql server in any particular string format. It is likely an integer or a decimal. Again, his issue has nothing to do with sql server, and everything with reading date correctly from csv file. – Ilia G Nov 23 '11 at 20:39

2 Answers2

3

In the dataflow add a derived column transformation. Create a new derived column and change its expression to

(DT_DBTIMESTAMP)(SUBSTRING(TextDate,7,4) + "-" + 
    SUBSTRING(TextDate,1,2) + "-" + SUBSTRING(TextDate,4,2))

You can then map this column to your sql server destination column

Clint Good
  • 820
  • 6
  • 14
0

Use a dataflow transformation Data conversion and choose DT_DATE as output type.

The string in input (from your Excel file) will be convert to date object (if your table column is not typed as date).

Otherwise, you can add a dataflow transformation script component to parse your string and transform it as demanded using C#...

Jean-Charles
  • 1,690
  • 17
  • 28
  • That would be only a half of the work required. Second half is to get SSIS to recognize that date value is stored in `dd/mm/yyyy` format. If data conversion has a setting for it, then great. Otherwise one would have to write a custom (script?) module. – Ilia G Nov 23 '11 at 20:34