CODEWITHSUNDEEP
sqlsql-serverexceldatetimedatetime-conversion
Nicolaesse
Nicolaesse

Reputation: 2714

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.

Upvotes: 1

Views: 11818

Answers (3)

GSazheniuk
GSazheniuk

Reputation: 1384

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.

Upvotes: 6

John Cappelletti
John Cappelletti

Reputation: 81990

Convert your DATETIME to a FLOAT and add 2

Example

Declare @D datetime = '2018-08-23 15:32:32.000'

Select cast(@D as float)+2

Returns

43335.6475925926

Upvotes: 4

user1443098
user1443098

Reputation: 7665

I'd just get the date from the Db and let Excel convert it. That way, if Excel ever changes its internal representation, your SQL code won't have to change

Upvotes: 0

Related Questions