Search code examples
sqlsql-serverexceldatetimedatetime-conversion

How to convert datetime (SQL Server) into Excel datetime?

The problem: I would like to convert datetime of SQL Server into Excel datetime format.

Example 1: from 2018-08-23 15:32:32.000 to 43335,65

Example 2: from 1985-03-26 10:35:42.000 to 31132,44

What I have tried: inspired by this answer, I tried this query

SELECT DATEDIFF(day, @MyDatetime, GETDATE())

which works if you want to convert a date (without the hours), but how to convert the time too?

In Excel, the time is saved in the decimal part of the number so I've tried to use 

SELECT DATEDIFF(SECOND, '1899/12/30 00:00:00.000', GETDATE())

but this results in an overflow error.

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Solution

  • You could use CAST:

    select CAST(GETDATE() as float)+2

    How Dates work in Excel: https://www.excelcampus.com/functions/how-dates-work-in-excel/

    Basically any date can be stored as number of days since 1/1/1900. And the time is fractional value which is equal to (number_of_seconds_since_midnight)*(1/(24*60*60))

    Need to add 2 days as Excel and SQL count number of days from a different start date.

    Hope this all makes sense.