Which is the best way to query data between 2 dates

Question

Which is the best way to query data between 2 dates

hnkumar

Old Hand

Points: 327
More actions
June 26, 2012 at 7:12 am

#258468

Hi,
I'm facing a strange problem. We have 2 queries to fetch data between dates. However both queries are giving different values. First query is giving 8987 records but second query is giving only 8972 records. When we actually compare and we found that few records (18th June 2012 7:22AM) are missing in 2nd query.
Query1 - select count(*) from NONMEMBER_TRANS WHERE cast(convert(varchar(10),[Uploaded Date],101) as datetime) BETWEEN '2012-06-18' AND '2012-06-24'
Query2 - select count(*) from NONMEMBER_TRANS where convert(datetime, convert(char(10), [Uploaded Date], 103), 103) between convert(datetime, '18/06/2012', 103) and convert(datetime, '24/06/2012', 103)
When I'd a discussion with vendor and he is explaining that query2 is best and whatever record counts give that is valid. But when i actually check data then Query1 results are right.
Can you please help me here and understand which is one is best way to use and any guess on why records are missing in 2nd query.
Regards,
hnkumar

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Hard to say with absolute certainty without seeing the ddl. Is this column a datetime datatype? If so you should not do any conversions at all.

The 2nd is just flat out wrong. It is converting a datetime to a string and then comparing the strings.

Assuming Uploaded Date is a datetime then a simple comparison is the best way.

[Uploaded Date] BETWEEN '2012-06-18' AND '2012-06-24'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

hnkumar (6/26/2012)
Hi,
I'm facing a strange problem. We have 2 queries to fetch data between dates. However both queries are giving different values. First query is giving 8987 records but second query is giving only 8972 records. When we actually compare and we found that few records (18th June 2012 7:22AM) are missing in 2nd query.
Query1 - select count(*) from NONMEMBER_TRANS WHERE cast(convert(varchar(10),[Uploaded Date],101) as datetime) BETWEEN '2012-06-18' AND '2012-06-24'
Query2 - select count(*) from NONMEMBER_TRANS where convert(datetime, convert(char(10), [Uploaded Date], 103), 103) between convert(datetime, '18/06/2012', 103) and convert(datetime, '24/06/2012', 103)
When I'd a discussion with vendor and he is explaining that query2 is best and whatever record counts give that is valid. But when i actually check data then Query1 results are right.
Can you please help me here and understand which is one is best way to use and any guess on why records are missing in 2nd query.
Regards,
hnkumar

Your vendor is wrong - both queries are poor.

Find out which dates are missing:

SELECT

[Uploaded Date],

x.ConvertedDate,

x.RangeStart,

x.RangeEnd

FROM NONMEMBER_TRANS

CROSS APPLY (

SELECT

ConvertedDate = cast(convert(varchar(10),[Uploaded Date],101) as datetime),

RangeStart = CAST('2012-06-18' AS DATETIME),

RangeEnd = CAST('2012-06-24' AS DATETIME)

) x

WHERE x.ConvertedDate BETWEEN x.RangeStart AND x.RangeEnd

GROUP BY [Uploaded Date], x.ConvertedDate, x.RangeStart, x.RangeEnd

SELECT

[Uploaded Date],

ConvertedDate = convert(datetime, convert(char(10), [Uploaded Date], 103), 103),

RangeStart = convert(datetime, '18/06/2012', 103),

RangeEnd = convert(datetime, '24/06/2012', 103)

FROM NONMEMBER_TRANS

CROSS APPLY (

SELECT

ConvertedDate = convert(datetime, convert(char(10), [Uploaded Date], 103), 103),

RangeStart = convert(datetime, '18/06/2012', 103),

RangeEnd = convert(datetime, '24/06/2012', 103)

) x

WHERE x.ConvertedDate BETWEEN x.RangeStart AND x.RangeEnd

GROUP BY [Uploaded Date], x.ConvertedDate, x.RangeStart, x.RangeEnd

Then Google "SARGable". Whichever query is returning the correct results should be adjusted so that the date filter is SARGable.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I'd say both queries have issues as well. Since you are apparently using SQL Server 2005 (this is a SQL Server 2005 forum you posted in) and are using the DATETIME data type, there is the possibility that your dates could have a time portion other than 00:00:00.000. Even if you are 100% certain that this is not possible, when working with the DATETIME data time (or SMALLDATETIME), you should consider the possibility.

The best way to query a range of dates is to use a closed end comparision on the low end and an open end comparision on the high end. Using the first query:

select count(*) from NONMEMBER_TRANS WHERE cast(convert(varchar(10),[Uploaded Date],101) as datetime) BETWEEN '2012-06-18' AND '2012-06-24'

it should look like this:

select count(*) from NONMEMBER_TRANS WHERE [Uploaded Date] >= '20120618' AND [Uploaded Date] < '20120625';

You will also note I took the hyphens out of the dates, and adjusted the upper date from 20120624 to 20120625. With the hyphens, this query would also fail if the DATEFORMAT were set to DMY.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Lynn Pettis (6/26/2012)
I'd say both queries have issues as well. Since you are apparently using SQL Server 2005 (this is a SQL Server 2005 forum you posted in) and are using the DATETIME data type, there is the possibility that your dates could have a time portion other than 00:00:00.000. Even if you are 100% certain that this is not possible, when working with the DATETIME data time (or SMALLDATETIME), you should consider the possibility.
The best way to query a range of dates is to use a closed end comparision on the low end and an open end comparision on the high end. Using the first query:
select count(*) from NONMEMBER_TRANS WHERE cast(convert(varchar(10),[Uploaded Date],101) as datetime) BETWEEN '2012-06-18' AND '2012-06-24'
it should look like this:
select count(*) from NONMEMBER_TRANS WHERE [Uploaded Date] >= '20120618' AND [Uploaded Date] < '20120625';
You will also note I took the hyphens out of the dates, and adjusted the upper date from 20120624 to 20120625. With the hyphens, this query would also fail if the DATEFORMAT were set to DMY.

+1

I think that any use of CONVERT to filter dates is just begging for an SQL nose bleed just like the one that happened in the original post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)