Héléna Héléna - 29 days ago 17
SQL Question

SQL Server : the date format changes to nvchar when Excel imported into SQL Server

I face a problem, the "orinal date" in Excel are in different formats, once importing into SQL Server, some will change to NULL. So I want to unify the format in Excel as the column F with a formular as shown below.

The thing is even the format has changed to "Date" in EXCEL for column F, this column is still shown as "nvarchar" after imported into SQL SERVER.

So how can I change the column to date in SQL?


  • I have tried to use
    SELECT DISTINCT CONVERT(VARCHAR(100), [Change to], 112)
    but as the code has many many date range criteria in the where clause, it is not very friendly to use so many convert clause for each of the where clause.

  • I have also tried to change the format from nvarchar to date when importing, but it reports errors.



The 2 methods don't function, so I am eager to have some advice from you.
Many thanks in advance for your help.

enter image description here

Answer

You can use something like this to convert to a datetime format varchar for import:

=year(e4) & "-" & right("0" & month(e4), 2) & "-" & right("0" & day(e4), 2) & "T00:00:00"

This will ensure that it uses the two digit format for month and day each time.

You can also use:

=date(year(e4), month(e4), day(e4))

This will convert it to an Excel date format.