Dates and Databases

A co-worker of admirable initiative is learning SQL in what is probably best way there is: to meet an immediate need. I have offered a clarification or two when asked, but as far as I can tell she has been mastering it quickly.

A while ago, though, a query that should have produced some rows produced none, and it was not clear why. I had a look at the query provided, where the final restriction was something like

ImportantDate BETWEEN 2015-01-01 and 2017-12-31

meaning "the important date occurred in 2016, 2016, or 2017".

Now, not all database engines will accept this. Oracle will reject it as an attempt to use a number where a date belongs. SQL Server, though, and evidently MySQL will happily read 2015-01-01 and 2017-12-31 as arithmetic expressions reducing to 2013 and 1974 respectively. Now, there is no number that is larger than or equal to 2013 and less than or equal to 1974, so such a condition will never allow rows to be returned. She dropped single quotation marks around the dates, and got what she needed.