How does SQL Server figure out the entered date's format?

user_10794582 0
Viewed 88

I use SQL Server 2014 Developer. Assume I have this scenario where I filter payment date of an Invoice

select * 
from Invoices 
where PaymentDate > '1/2/2012'

In the Invoice table, PaymentDate is in the format of 2012-01-08 00:00:00 ( YYYY-MM-DD HH:MM:SS )

But RHS operand of the where is 1/2/2012 So, how does SQL know if this in the format of M/D/YYYY or D/M/YYYY ?

date-format tsql sql-server sql database
1 Answers

Datetimes don't have a format (other than binary): they are (hopefully) being stored as datetime and not strings. Don't confuse presentation with representation.

When you have a string literal, '1/2/2012', SQL Server interprets via your locale.

Best practice to always use (and export) dates in the ISO 8601 format yyyy-MM-dd (yyyyMMdd).

See ISO 8601
What date/time literal formats are LANGUAGE and DATEFORMAT safe?

user_160760
Related