How can I change the data mapping?

Question

I'm importing a file via excel into a table in SQL. I keep getting this error about the date time. I've tried changing the date format in Excel and nothing helps. Also I can't change the format type either.

Answer 1

Hi @Danielle,

Thank you for reaching out and welcome to Microsoft Q&A.

Do you mean you want to Import date from excel with right format conversion to SQL Server?

You should import the date into the table as nvarchar(10) and then convert it after importing the date.

In general, the nvarchar type structure, also known as a staging table, is followed for all purposes. In the staging table, most of the relevant untrusted data fields are of type nvarchar(N), which allows them to be successfully imported into SQL Server.

Once imported, you should put the data from the staging table into the desired table using the correctly transformed/transformed columns from the MERGE or UP-SERT query.

In this case, you should use an explicit conversion, for example:

CONVERT(DATE,your_staging_date_column,110)

If you set the data to "Date" in Excel, you can convert it to nvarchar and then restore the date as follows:

CONVERT(DATE,CONVERT(nvarchar,[DATE FROM excel),110)

The last 110 means it will be converted to mm-dd-yyyy format because your data is currently formatted in Excel.

If you want to read data in plain text from Excel, then it is enough to convert it to date:

CONVERT(DATE,[DATE FROM excel], 110)

This function converts the data into the correct format in the table.

In addition, please check out this article, we can get the information from it:

NOTE: You mentioned that you keep getting this error about the date time. As Erland says, could you please show us the detailed error messages and your actions to help us narrow down the issue?

Feel free to share your issue here if you have any confusions.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

changing the date format in Excel

Excel does not really know data types, only a value and a format defintion.

Have a look "Source/Destination types", the marked ones are different and that can cause problem.

Better import the Excel data first into a staging table with all columns of typ "nvachar(255)" and then model/convert the data in SQL.