Sql Server - Filtering Date and Time

From MyWiki
Jump to: navigation, search

A logon with us_english will have the DATEFORMAT setting set to mdy, British to dmy, and Japanese to ymd. The problem is, how do you as a developer express a date if you want it to be interpreted the way you intended, regardless of who runs your code?
There are two main approaches. One is to use a form that is considered language-neutral. For example, the form '20070212' is always interpreted as ymd, regardless of your language. Note that the form '2007-02-12' is considered language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET. Unfortunately, due to historic reasons, this form is considered language-dependent for the types DATETIME and SMALLDATETIME. The advantage of the form without the separators is that it is language-neutral for all date and time types. So the recommendation is to write the query like the following.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate = '20070212';
===
WHERE orderdate BETWEEN '20070201' AND '20070228 23:59:59.999'