CODEWITHSUNDEEP
sqlsql-server
Héléna
Héléna

Reputation: 1095

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?

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

Upvotes: 0

Views: 161

Answers (1)

BishNaboB
BishNaboB

Reputation: 1070

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.

Upvotes: 1

Related Questions