Search code examples
sqlsql-servert-sqlsql-server-2012date-manipulation

SSMS 2012: Convert DATETIME to Excel serial number

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

Solution

  • 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