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
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)