What are the challenges you face when working across database platforms? Take the survey

Date conversion failure resulting in NULL content when importing from existing csv-file

I'm having a problem when importing dates data from a pipe-delimited text file using SQL Data Generator. The destination is an existing table in a local instance of MS SQL EXPRESS and the underlying data type of the date fields is "datetime2".

I have a text file with several date columns in the format MM/dd/yyyy. I can't get any of them imported into my SQL database.

On the second page of the "CSV File Data Import Settings" dialog window I select a date column and in the "Column type" box select "DateTime" and choose 'MM/dd/yyyy" from the combo box as a date format. When I then select a different (non-date) column and go back to selecting the date column again, the "DateTime" option is still selected, but the date format has changed to the default "yyyy/MM/dd hh:mm:ss". No matter what I try, it seems my date format selection of "MM/dd/yyyy" just does not stick.

Strangely, if I select a different date column after having selected "MM/dd/yyyy" for my first date column, the "DateTime" option stays selected for the second date column with "MM/dd/yyyy", even though I never selected it for the second date column. But as soon as I select another non-date column with the default "Automatic" option set and then go back to selecting either one of the two date columns, the date format is set back to "yyyy/MM/dd hh:mm:ss" for both.

When I leave the dialog window by pressing the "Finish" button I see that in the preview the date columns are NULL for all rows and after importing the data the date columns are indeed NULL for all rows.

What am I doing wrong? Is there a way to import dates from a text file into fields of type datetime2? 
Sign In or Register to comment.