CODEWITHSUNDEEP
sql-serverexceldatetimeformatted
Archias
Archias

Reputation: 383

Datetime value in Excel unexpectedly changes when imported into SQL Server 2012

I have an issue with my data import from Excel to SQL Server. The datetime value being imported into the destination table is different from the datetime value in the Excel source file.

With or without any formatting the value is always .003 milliseconds less than the actual time in Excel. This causes values that should be marked for 1 AM to be marked for 12 AM when attempting to GROUP BY hour.

Notice my sample query & results to see the exact values.

If someone could tell me why this is happening and how to get my expected results it would be greatly appreciated.

I would also like to resolve this without any additional steps. (No staging tables please)

SELECT          
    Timestamp,
    CAST(Timestamp AS DATE) Date,               
    CAST(Timestamp AS DATETIME) Datetime,
    CAST(Timestamp AS DATETIME2) Datetime2,
    CAST(Timestamp AS TIME) Time
FROM
    OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=Yes;
                Database=\\server\share\160322.xlsx;',
                'SELECT * FROM [160322$]')

/* Query Results (ALL WRONG):    
Timestamp   :   2016-03-22 00:59:59.997     -- Imported Value without formatting

Date        :   2016-03-22                  -- Formatted Values
Datetime    :   2016-03-22 00:59:59.997
Datetime2   :   2016-03-22 00:59:59.9970000
Time        :   00:59:59.9970000
*/

Value in Excel: 

3/22/2016  12:15:00 AM

Value in SQL Server table: 

2016-03-22 00:14:59.997

Expected SQL Server value:

2016-03-22 00:15:00.000

Value in Excel: 

3/22/2016  01:00:00 AM

Value in SQL Server table: 

2016-03-22 00:59:59.997

Expected SQL Server value:

2016-03-22 01:00:00.000

Upvotes: 2

Views: 840

Answers (1)

marc_s
marc_s

Reputation: 754240

The DATETIME datatype in SQL Server has an accuracy of 0.003 seconds - 3.33 milliseconds - that's a well-known and documented fact (see here on MSDN and here a blog post ).

You only get values like .000, .003, .007, .010, .013 etc. - DATETIME does not support values down to the millisecond.

However, using DATETIME2(3) should fix that problem (unless the importing from Excel using OPENROWSET somehow mangles that)

Upvotes: 1

Related Questions