Insert new rows with a Excel column with Date format

Discussion of issues, suggestions and bugs of Devart Excel Add-ins, our product line for connecting Excel to external data from cloud applications and databases
Post Reply
Cortlandt
Posts: 2
Joined: Tue 23 Apr 2019 17:01

Insert new rows with a Excel column with Date format

Post by Cortlandt » Thu 25 Apr 2019 00:11

I have 100's of rows of data in Excel where one of the columns in the Excel sheet has a "Date" data type which writes to a SQL Server "datetime" data type column.

When I insert more than one row at a time from another Excel sheet the 3rd or forth row inserted reverts back to Excel's default "general" data type. This change of data type in the cell causes a error.

Notes: A cell with a "date" data type will display a date such as "2019-04-01" whereas the same cell will display something like "12003" when the cells data type is "general".

Pinturiccio
Devart Team
Posts: 2418
Joined: Wed 02 Nov 2011 09:44

Re: Insert new rows with a Excel column with Date format

Post by Pinturiccio » Thu 25 Apr 2019 15:33

This is Excel's behaviour. You can reproduce it without Devart Excel Add-in.
1. Start Excel without using Devart Excel Add-in for SQL Server.
2. Copy 2019-04-01 to any cell.
3. Change the format of this cell to general.

Excel will change this value. Thus, when you insert a date value into a cell with the general format, then this value will be converted to a number which is the number of days starting from January 1, 1900. For example the value 12003 corresponds to date 10-11-1932. When you paste a date value to a cell with the general format, it is converted to a number, and when you insert a number to a cell with the date format, this value is converted to the corresponding date.

Post Reply