Sql Server - Filtering Date and Time
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'