SSMS 2012: Convert DATETIME to Excel serial number

Question

I can't seem to find an answer to this anywhere --- I want to convert a datetime in SQL to the excel serial number.

I'm essentially looking for the DATEVALUE function from excel but for use in SQL

Any ideas on how to do this? thanks

Answer 1

Assuming the desired date is 2016-05-25

Select DateDiff(DD,'1899-12-30','2016-05-25')

Returns

 42515

If you want the time portion as well

Declare @Date datetime = '2016-05-25 20:00'
Select DateDiff(DD,'1899-12-30',@Date)+(DateDiff(SS,cast(@Date as Date),@Date)/86400.0)

Returns

42515.8333333

Answer 2

You just need to convert your datetime value to int and add 1:

SELECT CONVERT(INT,YourDate) + 1
FROM dbo.SomeTable;

Answer 3

With collation: SQL_Latin1_General_CP1_CI_AS

you should use

Select DateDiff(DD,'18991230','20160525')

Returns 42515

You could replace '20160525' for getdate() or you date field

Answer 4

I ran into this issue and found the most elegant solution to be the following (as others have mentioned, adding the +2 is essential due to the differences between SQL and Excel dates):

Assuming the "Column" is a DateTimeOffset:

SELECT CAST(CAST(COLUMN_TO_BE_CONVERTED as datetime)+2 as float) as EXCEL_DATE_FLOAT

If the column is not already a DateTimeOffset and is just a DateTime, you would not need the double cast; you'd just need something like:

SELECT CAST(COLUMN_TO_BE_CONVERTED+2 as float) as EXCEL_DATE_FLOAT

The resultant float value in either case is what excel recognizes as the date and time and you can easily extract what you need from there. Upon manually verifying the results in excel, I confirmed that the serial numbers matched the date and time exactly as I expected.