Difference Between Datetime Converts in Msexcel and SQL Server

Difference between datetime converts in MSExcel and SQL Server

hehe ;) one day ages ago I wondered the same thing... do a simple exercise:

compare Select Cast(0 as DateTime) vs. =DATEVALUE("1900-01-01") which explains 1 day difference

and find the one extra leap year by reading the father of VBA, Joel Spolsky, explanation

tl;dr

check out the difference - which exlpains the 2nd day

=DateValue("1900-02-28") and =DateValue("1900-03-01")

SQL Server and excel date to number conversion give difference

I think there are 2 reasons.

The first date with a serial number in Excel is 1900-01-01. This has a serial number of 1. https://support.office.com/en-gb/article/DATEVALUE-function-df8b07d4-7761-4a93-bc33-b7471bbff252
In SQL this date has a numeric number of 0 not 1. So that accounts for a difference of 1.

The second unit is a bit odd. In Excel the date 1900-02-29 has a serial number even though that date didn't exist (29th Feb exists every 4 years except at the turn of a century). In SQL 1900-02-29 doesn't exist (try doing this SELECT CONVERT(int, CONVERT(datetime, '19000229'))). So that accounts for the second unit difference.

integer to date conversion issue in SQL vs Excel

SQL Server uses a different date format.

I believe the correct conversion is:

select dateadd(day, 43251, '1899-12-30')

This is confusing. Excel treats 0 as 1900-00-00, which means that 0 is 1899-12-31 and 1 is 1900-01-01. That should suggest that the conversion is:

select dateadd(day, 43251, '1899-12-31')

However, that does not produce the correct date. Why not? Excel has incorrect leap year arithmetic. So, it treats day 60 as 1900-02-29. However, 1900 is not a leap year, so "60" should be 1900-03-01.

The rules for leap years are:

• Years that are divisible by 4
• Except years that are divisible by 100
• Except years that are divisible by 400

So, 2000 was a leap year, but 1900 and 2100 are not.

In other words, the dates are correct only for dates after 1900-03-01.

Variance in Dates in SQL Server, Excel and MS Access

It's because they use different origin dates to define a datetime.

If you want to cast a datetime field to an integer, always choose your own origin date.

For example, when you want to use 1970-01-01 as a reference date, use:

SELECT DATEDIFF(d, '1970-01-01', '2021-01-01');

And if you want to cast it back to a date:

SELECT DATEADD(d, 18628, '1970-01-01')

Never rely on implementation details like the default origin date.

Note that you can easily get the origin date by using SELECT CAST(0 AS DATETIME) for SQL server (1900-01-01) or SELECT FORMAT(CDATE(0), "Short Date") in Access (1899-12-30), which reveals the 2-day difference.

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

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.

CONVERT vs CAST when exporting datetime as dates from sql server

To answer your questions sequentially:

1. 126 uses the ISO 8601 date standard, which signifies the Year aspect to be the full 4 characters long, rather than just the last two. 127 uses the same date standard, but in Time Zone Zulu, which is a military time zone (4 hours ahead of EST)

2. There essentially is no difference when copy/pasting to Excel. When opening an Excel doc, the default cell formatting is "General". When you paste any of these date types into Excel, it will register option A as a number (in this case 41270) and based on the pre-existing format from your query will convert it to Date format. Options B and C will first register as text, but since they are in the format of a Date (i.e. they have the "/" marks), Excel can register these as dates as well and change the formatting accordingly.

3. As long as the person you are sharing your script with uses T-SQL this shouldn't cause problems. MySQL or other variations could start to cause issues.

4. CAST(createdat as date) is the best option (IMO)

Sources:

SQL Conversion Types

ISO 8601 Details

Zulu Time Zone

Excel 5 digit Datetime converted in SQL is 2 days ahead

Excel dates are tricky. What they do is count the number of days since Dec 30th, 1899 (and early years are not entirely accurate).

One option is:

dateadd(d, 28540, '1899-12-30')

Demo on DB Fiddle:

 select dateadd(d, 28540, '1899-12-30') new_dt

| new_dt                  |
| :---------------------- |
| 1978-02-19 00:00:00.000 |

convert Excel Date Serial Number to Regular Date

In SQL:

select dateadd(d,36464,'1899-12-30')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)

In SSIS, see here

http://msdn.microsoft.com/en-us/library/ms141719.aspx

Sql server and Excel float to date offset

SQL server simply calculates the conversion of a date time to a float as the number of days since midnight on 01-Jan-1900 (i.e. select convert(DATETIME, 0) gives 1900-01-01 00:00:00.000)

Excel calculates a similar number, but the zero date is "00/01/1900". This is probably related to the fact that excel uses one based indexing, rather than the more common zero based indexing. The second day of difference comes from a well known bug whereby excel considers 1900 to have been a leap year.

Takeaway message: if you assume that excel is always behind by two days you'll be ok, except for dates on or before the 28th of February 1900.

---

Related Topics