Date conversion issue in SQL Server 2012

Question

I am trying to convert numeric value to date. I have search for it found below answer

SELECT CONVERT(DATETIME, CONVERT(FLOAT, 41547))

Output which I got is 2013-10-02 00:00:00.000

But if I check the date value of 41547 in excel It gives me 2013-09-30, which is 2 days less than what I get in SQL Server.

Please help me to understand this conversion.
How can I get 2013-09-30 instead of 2013-10-02 for number 41547.

Answer 1

Excel treats the mythical date 01/00/1900 (i.e., 12/31/1899) as corresponding to 0, and incorrectly treats year 1900 as a leap year. So for dates before 03/01/1900, the Excel number is effectively the number of days after 12/31/1899.

Excel will not format any number below 0 (-1 gives you ##########) and so this only matters for "01/00/1900" to 02/28/1900, making it easier to just use the 12/30/1899 date as a base.

Reference from this post

So in your SQL Server you need to add the days (41547 in your case) from the 12/30/1899 like the below and it will give the same result as return from the excel.

SELECT DATEADD(DAY, 41547, '1899-12-30')  

It will result as 2013-09-30 00:00:00.000

If you don't want the timestamp in the result and expect the same format from excel 2013-09-30 then use the convert as

SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY ,41547, '1899-12-30'), 111), '/', '-')