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

Loewe

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

Ulterior

I realize this is an old post, and I apologize for resurrecting it, but it is strange. If @Date is '1900-01-01', the DateDiff returns 2 while Excel returns 1. There must be some point in time when an additional day is added. Does anyone know what that day is and why? – Vaudevillian

@InterstellarProbe Just one of those little inconsistancies in life. Excel treats 0 as 1900-00-00 which is really 1899-12-31 while SQL Server sees day 0 as 1900-01-01 ... Just for fun, try Select convert(datetime,0) – Ulterior

I figured it out. Excel treats the year 1900 as a leap year (so 60 is converted to 2/29/1900), but that date does not exist in the Julian calendar. So, for dates between 1/1/1900 and 2/28/1900 (1 through 59), you need to use DateDiff(DD,'1899-12-31',@Date). – Vaudevillian

@InterstellarProbe Good to know if I'm ever doing any EARLY 20th Century date calculations from Excel. :) – Ulterior

I was worried it was going to wind up being an issue like the Energy Policy Act of 2005 which changed when Daylight Savings Time started and ended or some other more recent policy. So, I wanted to know when the extra day started to make sure I did not screw up any calculations from this century. Basically, it just confirmed that the formula you presented will work for all dates I care about, and now I know why it did not work when I tried it with Excel date 1. – Vaudevillian

I found this blog post that explains it (if you are at all interested): wambooli.com/blog/?p=3809 – Vaudevillian

@InterstellarProbe If only the "normals" knew what we have to wrestle with and be concerned about on a daily basis, they would build monuments to us. :) – Ulterior

Answer 2

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.

Zelig

Answer 3

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

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

Mangrum

Actually, it seems that you need to add +2 in order to match the number returned by Excel (?) If it were not for that, this method would be more elegant than the DateDiff() method. – Sing

Answer 4

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

Poundage