CODEWITHSUNDEEP
sqlsql-serverexceldate
user8165644
user8165644

Reputation: 43

Importing date data from excel into table with right format conversion

I am importing data from excel files into SQL server database. In excel ,Date field are in the format mm-dd-yyyy. Whereas SQL database imports it as yyyy-mm-dd, swapping dates to months.For ex. in excel its 01-12-2018 but in SQL it imports as 2018-12-01. Hence the dates are incorrect.

Please let me know a way to import it correctly in SQL. I am happy to import in both ways : yyyy-mm-dd or yyyy-dd-mm. I just want it to read the date correctly form excel file.

I am using import export wizard.

Upvotes: 1

Views: 24936

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17146

Updated answer: OP mentioned in another answer comment

I am currently using import export wizard to import data.

To solve problem in Import export wizard.

  1. Choose Flat file source - I used a sample file (sample.csv) with this data

    id, dates, text 1,08-09-2018,"sample" 2,05-09-2019,"more sample" Choose data step- advanced tab

  2. Under Choose your data source I went to Advanced tab and ensured that dates are imported as string. enter image description here
  3. At the Select Source Table and Views step, go into Edit Mappings>Edit SQL and then change the table creation query to have an extra calculated column (say cdates) with definition like [cdates] as convert(date,[ dates],110). As you can see I added this in my SQL as last column in definition.

In the case that you don't create table but insert into existing table. Alter the table to have a calculated column over the varchar date column.

See the output I got enter image description here

Original Answer: You should import the dates as nvarchar(10) into the table and then cast them after they have been imported.

Typically nvarchar type structure is followed for all purpose and is also called staging tables. In staging tables most of the relevant mistrusted data fields are of nvarchar(N) type allowing them to be successfully imported into SQL server.

After import, you should take data from staging tables into desired tables using properly casted/converted columns in a MERGE or UP-SERT query.

In you case you should use explicit convert like

CONVERT(date,your_staging_date_column ,110)

Upvotes: 2

Caldazar
Caldazar

Reputation: 3802

If data is set as Date in Excel, you can convert it to nvarchar, and then back to date as follows:

CONVERT(date, convert(nvarchar, [date from excel]), 110);

110 in the end means that it will convert from format mm-dd-yyyy as your data is currently formatted in Excel.

If you are reading data from Excel as pure text, then it will be enough to convert that text to date:

CONVERT(date, [date from excel], 110);

This function will convert data to correct format in your table.

Upvotes: 0

Related Questions