TSQL what is the difference between using dd-mm-yyyy and yyyy-mm-dd

Question

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.

Answer 1

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.