Shekhar Shekhar - 2 months ago 6
SQL Question

SQL Server date range query not working

I want to run following two SQL queries:

Take row count between

1 Oct 2014 to 31 Dec 2014
. For this I ran this query.

select count(*)
from table_name
where date_column > '30-Sep-2014 23:59:59.999' and date_column < '01-Jan-2015 00:00:00.000'


For which I got result
28674262


Second query is to get row count between
1 Jul 2014 to 31 Sep 2014
for which I ran following query.

select count(*)
from table_name
WHERE date_column > '30-Jun-2014 23:59:59.999' and date_column < '01-Oct-2014 00:00:00.000'


and I got this result :
28296043


Now to get the row count for
1 Jul 2014 to 31 Dec 2014
time period I ran following query.

select count(*)
from table_name
WHERE date_column > '30-Jun-2014 23:59:59.999' and date_column < '01-Jan-2015 00:00:00.000'


This query is giving result
57361505
.

If I add result of 1st and second query, it should give the same row count as 3rd query, that is
57361505
but I am getting
28674262
+
28296043
=
56970305


I would like to know why there is difference between row counts? Is there anything wrong with my queries?

Answer

You would seem to have some records, say 200, where the date is between '30-Sep-2014 23:59:59.999' and '01-Oct-2014 00:00:00.000'. This is possible depending on the types of the date/time values.

Get rid of the time stamps! They are just confusing things. Use these conditions:

where date_column >= '2014-10-01' and date_column < '2015-01-01'
where date_column >= '2014-07-01' and date_column < '2014-10-01'
where date_column >= '2014-07-01' and date_column < '2015-01-01'

In general, when using dates, use comparisons on the date with no time component and use inequality conditions where appropriate.

Also notice that I switched to an ISO standard date formats.

Comments