Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?

by

Maintaining compatibility with Lotus 1-2-3 back in the day, which had a bug in that it thought the year 1900 was a leap year (or pretended?).

The explanation is too long to quote, but for the sake of curiosity, here are some snippets.

1900 wasn’t a leap year.

“It’s a bug in Excel!” I exclaimed.

“Well, not really,” said Ed. “We had to do it that way because we need to be able to import Lotus 123 worksheets.”

“So, it’s a bug in Lotus 123?”

“Yeah, but probably an intentional one. Lotus had to fit in 640K. That’s not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That’s really fast and easy. The Lotus guys probably figured it didn’t matter to be wrong for those two months way in the past. It looks like the Basic guys wanted to be anal about those two months, so they moved the epoch one day back.”

Actually, this number is one greater than the actual number of days. This is because Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a “bug”. Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123. When Excel was introduced, 123 has nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus’ bug, in order to fully compatible. Users who switched from 123 to Excel would not have to make any changes to their data. As long as all your dates later than 1900-Mar-1, this should be of no concern.

More Related Contents:

  1. How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?
  2. Calling stored procedure using VBA
  3. #DELETE viewing SQL Server table in Access
  4. SQL Server Passthrough query as basis for a DAO recordset in Access
  5. How to make a passthrough / passthru query editable?
  6. ODBC Call Failed with stored procedure – Pass through query
  7. How to multiply column in Sql
  8. What is the best way to create and populate a numbers table?
  9. Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?
  10. How do you clear the SQL Server transaction log?
  11. Order Of Execution of the SQL query
  12. How Can I Log and Find the Most Expensive Queries?
  13. How to drop a table if it exists?
  14. SQL Server truncation and 8192 limitation
  15. Connecting to SQL Server LocalDB using JDBC
  16. How to check if a database exists in SQL Server?
  17. determining the character set of a table / database?
  18. error when inserting into table having instead of trigger from entity data framework
  19. How to query values from xml nodes?
  20. How to split string and insert values into table in SQL Server
  21. Case sensitive variables in SQL Server
  22. How to query for Xml values and attributes from table in SQL Server?
  23. Full text search does not work if stop word is included even though stop word list is empty
  24. Setting up maven dependency for SQL Server
  25. Getting a warning when using a pyodbc Connection object with pandas
  26. In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?
  27. How can I get the list of tables in the stored procedure?
  28. How to find the boundaries of groups of contiguous sequential numbers?
  29. Conditional JOIN Statement SQL Server
  30. How do you deal with blank spaces in column names in SQL Server?

Leave a Comment