4

I have a rather basic question out of interest. In T-SQL (SSMS or the like), what is the difference, "behind the scenes", between using dd-mm-yyyy and yyyy-mm-dd in a where statement.

For example, these two queries give me different results

select * from DB..table where application_date > '01-01-2019' and application_date < '01-06-2019' order by app_ID;

select * from DB..table where application_date > '2019-01-01' and application_date < '2019-06-01' order by app_ID;

It seems that the first is a sub set of the second. For comparison, the first gives me 83 records, while the second gives me over 11 thousand with change.

It would be interesting to understand, if someone could enlighten me.

GenDemo
  • 749
  • 1
  • 7
  • 24

2 Answers2

6

String representations of datetime values are culture dependent.
In USA, for instance, the string representation format is MM/dd/yyyy, while in UK it's dd/MM/yyyy.
This means that SQL Server will convert string literals to datetime based on the culture settings, specifically, it's DATEFORMAT - Unless the string representation format complies to the ISO 8601 standard - which is either yyyy-MM-ddTHH:mm:ss or yyyyMMddTHHmmss.

However, there is a little known bug (or feature) with the DateTime data type, when converting date-only values from string formatted as yyyy-MM-dd, the conversion still depends on the culture settings - while converting from the alternate ISO 8601 format yyyyMMdd is perfectly safe for all datetime data types.

This is probably because this format is not unique to ISO 8601 - it's also a part of the ODBC canonical format - which is yyyy-MM-dd HH:mm:ss - almost the same as ISO 8601, just with a space instead of the T as a separator between the date part and the time part.

This bug only exists in the DateTime data type, and is one of several reasons why you should always prefer to work with the newer and improved DateTime2 data type.

for more information, read Date conversion and culture: Difference between DATE and DATETIME.

TL;DR;
The only safe string representation date format to work with datetime is yyyyMMdd.
all other formats conversions are culture dependent and might yield errors or worst - wrong results.

Main points:

  • When you can, prefer DateTime2 over datetime.
  • When you can, prefer avoiding string representations of datetime.
  • When you have to work with string representations of datetime, Always work with one of the ISO 8601 formats - either yyyy-MM-ddTHH:mm:ss or yyyyMMddTHHmmss
  • When you have to work with DateTime and string representations of date only, always use yyyyMMdd.
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
4

The literal '01-06-2019' is actually interpreted to be 2019-01-06, which is not the same thing as 2019-06-01, which the second query is using.

You may read SQL Server's documentation covering supported date literal formats. But in general, try to stick with ISO date formats, which are supported by pretty much every SQL database. So, the second version of your query is the one you should always try to use.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • In my experience the only format that is always interpreted correctly is `'20190601'` The ISO format with dashes (`'2019-06-01'`) does not always work. –  Mar 21 '19 at 06:43
  • @a_horse_with_no_name MySQL is fairly lax, and the dashed version would work, but thanks for commenting. – Tim Biegeleisen Mar 21 '19 at 06:45
  • Yes, if it's going to be converted to `datetime` rather than `datetime2` or other similar modern types, `nnnn-nn-nn` is unsafe. `set dateformat dmy select MONTH(CONVERT(datetime,'1992-02-06'))` returns 6 still. – Damien_The_Unbeliever Mar 21 '19 at 06:54
  • I was talking about SQL Server, not MySQL –  Mar 21 '19 at 07:00
  • 1
    In SQL Server there's a difference between the `DateTime` data type and `DateTime2` / `DateTimeOffset` / `Date` and `Time` data types with the dashed version of the ISO 8601 standard. The `DateTime` data type conversion depends on laugnage/dateformat settings for converting `yyyy-mm-dd` while the other data types will *always* convert it properly. Read [Date conversion and culture: Difference between DATE and DATETIME](https://stackoverflow.com/questions/45792766/date-conversion-and-culture-difference-between-date-and-datetime) for more details. – Zohar Peled Mar 21 '19 at 07:08
  • @TimBiegeleisen Thank you, that is nice and simple. :) – GenDemo Mar 21 '19 at 22:01