Queries on date related data
are very common in all
kind of applications.But when
we query on date
columns we should be aware of how it works in
background.If
data of format date
is stored in column which is varchar datatype, we
should know when implicit inversion would take place.Otherwise
we could get unexpected results.
--create table
CREATE TABLE #GET_DATE(DT VARCHAR(50))
--insert date
values
INSERT INTO #GET_DATE VALUES ('11/01/2011'),('12/01/2012'),('10/01/2013')
SELECT * FROM #GET_DATE
Output:
DT
----------
11/01/2011
12/01/2012
10/01/2013
--check for dates less than
or equal to '10/01/2013' (3 results expected)
SELECT *
FROM #GET_DATE
WHERE CONVERT(VARCHAR(50),DT,101)<=CONVERT(VARCHAR(50),GETDATE(),101)
Output:
DT
----------
10/01/2013 --wrong
output
--with implicit conversion
of DT column
SELECT *
WHERE DT<=GETDATE()
Output:
DT
----------
11/01/2011
12/01/2012
10/01/2013
In the first query it is
actually checking string values and not a date. So, the result what we got is
based on string values comparison.