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?
SELECT DISTINCT CONVERT(VARCHAR(100), [Change to], 112)
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.