CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldate-format
tm_ello
tm_ello

Reputation: 33

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

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 ?

Upvotes: 3

Views: 106

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300529

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?

Upvotes: 7

Related Questions