Search code examples
sql-serverexcelms-access

Variance in Dates in SQL Server, Excel and MS Access

I noticed that when I convert a date into an integer, I get different values in SQL Server, Excel and MS Access.

For example, in SQL server, SELECT CONVERT(INT,CONVERT(DateTime,CONVERT(varchar, '2021-01-01'))) AS MyDate; gives me 44195.

In Access, SELECT CLng(#1/1/2021#) AS MyDate; gives me 44197.

In Excel, =DATEVALUE("1/01/2021") also gives me 44197.

Why is the SQL server result less by 2?

Solution

  • 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.