No results returned when searching between specific years? 1

[gk17]

I can't figure out what the problem is. Using SQL Server 2005 to run the below query:

SELECT *
FROM Explorer E
JOIN ReportEvent RE ON E.ReportID = RE.ReportID
JOIN Report R ON E.ReportID = R.ReportID
JOIN "Order" O ON E.OrderID = O.OrderID
WHERE convert(varchar(10), O.CompleteDate, 101) BETWEEN '09/01/2013' AND '03/31/2014'

I'm getting no results when I search for the above range. The weird thing is if I break them down from 9/1 to 12/31 and then 1/1 to 3/31, it works for both. I tried searching different years and don't have this problem. It seems to be that range only. I also tried going back a month at a time 8/1, 7/1..etc. and have the same problem until I hit 02/01/2013 to 03/31/2014. I get results returned but only for 2014 records. I confirmed that I have other for all the other months in between.

Has anyone seen this before? It makes me think at times that the query is not refreshing properly. Is there any way to "force" a refresh on the query? I tried closing the sql file and opening it up again without any luck.

Thanks.

 

[djj55]

Converting a DATE to VARCHAR is not a good thing. Try without the convert and see what you get.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

djj is absolutely correct about the varchar problem. When you compare the dates as string, it will do an alphabetic comparison.

try this:

WHERE O.CompleteDate BETWEEN '09/01/2013' AND '03/31/2014'

Also be aware that there are international issues with dates. Therefore, the safest way to use dates in SQL Server is YYYYMMDD, so this query becomes.

WHERE O.CompleteDate BETWEEN '20130901' AND '20140331'

Last, but not least... if your CompleteDate includes time, then the query above will not return all of the data you expect it to because CompleteDate could be 03/31/2014 3:00 PM. The way the date is hardcoded in the query, it really means 03/31/2014 at midnight (before the day actually starts). Therefore, it is usually best to do this...

WHERE O.CompleteDate >= '20130901' 
      O.CompleteDate < '20140401'

Basically, you use the less than operator for the end range but add 1 day. Trust me when I say that this will ALWAYS return the data you expect it to, and it will do it more efficiently so that your query will run faster. win-win.

BTW, there's no such concept as refreshing a query. It will always execute and operate on the data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George for the detail. I tend to be very brief.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gk17]

Thank you both for your quick replies. That solution worked perfectly and I will remember to leave the date format alone. It's date and time so I used the last code posted.

On the side. Maybe it's just SQL Server 2005 that's a bit of a pain to work with but when I do a SET ROWCOUNT 10 for example, it will return 10 records even after the fact when I remove it. So I manually set it to 0 for those queries. That's why I mentioned if there's a way to "refresh". Sorry for the incorrect term (or non-existent in this case LOL). Still on a learning curve here and appreciate all the help you guys provide.

 

[djj55]

If you are trying to get only 10 records look at TOP(10).

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

The "set" statements affect behavior. In this case, it affects the number of rows that are returned.

set statements affect the connection to the database engine. In SSMS a connection is created when you open a query window. The connection remains active until you close that query window. My point is, if you open another query window, you will get a new connection and therefore the set operations you ran in the first window will not affect the other window.

I understand your frustrations. Learning SQL Server is no easy task, especially if you are learning it "by fire". In any case, you can always post questions here and we will do what we can to help.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Thanks for explaining the reason why SET was working the way it is. I will also use TOP from now on to return limited records.

 

[flapeyre]

To expand on George's answer:

WHERE O.CompleteDate BETWEEN '09/01/2013' AND '03/31/2014'

will never return any result, because '09' is greater than '03'. So, what I do would be this:

declare @startdate datetime, @enddate datetime
set @startdate = '09/01/2013' 
set @enddate = '03/31/2014'
SELECT *
FROM Explorer E
JOIN ReportEvent RE ON E.ReportID = RE.ReportID
JOIN Report R ON E.ReportID = R.ReportID
JOIN "Order" O ON E.OrderID = O.OrderID
WHERE convert([COLOR=#EF2929]DATE[/color], O.CompleteDate) BETWEEN @startdate AND @enddate

The conversion of O.Completedate to a date data type effectively strips out the timestamp, so now you are comparing dates only. The fact that @startdate and @enddate ate datetime instead of date therefore doesn't matter. This is how I code a stored procedure that has date range variables for a report. It should work for Sql Server 2008 and up.



-- Francis
Francisus ego, sed non sum papa.

 

[gk53]

flapeyre right id you define CompleteDate as varchar or char, but it willwork ib you define as date or datetime

 

[flapeyre]

gk53, I was assuming that CompleteDate was datetime. What gave that away is the clause in the original query:

WHERE convert(varchar(10), O.CompleteDate, 101) BETWEEN '09/01/2013' AND '03/31/2014'

-- Francis
Francisus ego, sed non sum papa.

 

[gk17]

That's correct. CompleteDate is datetime type. I'm using George's suggestion since it's the most straight forward unless you're method can make the query run faster? It's not a speed issue so far with this query I have, but if I run into one and it takes a while, I will consider that.

 

[gmmastros]

flapeyre's query would run faster than the original one (even if the original one returned the correct data).

I just checked this....

If there is an index on the date column (CompleteDate in this case), then SQL Server would be able to use my query to perform an index seek. Normally, performing calculations on a column prevents SQL Server from using an index seek. With Flayeyre's query, there is a calculation on the column (converting it to the Date data type). However, in this case SQL Server can still use an index to perform a seek operation.

That does not mean that both queries are equivalent in performance. I ran the following query on a table in my database. The database engine is SQL2008R2.

Declare @Start DateTime
Set @Start = GetDate()

Select  * 
From    Calendar
Where   Convert(Date, CalendarDate) Between '20140301' and '20140331'

Select 'Flapeyre', DateDiff(Millisecond, @Start, GetDate())
Set @Start = GetDate()

Select  * 
From    Calendar
Where   CalendarDate >= '20140301' 
        and CalendarDate < '20140401'

Select 'George', DateDiff(Millisecond, @Start, GetDate())

The only difference in the queries is the where clause. This is not a true test because there are other factors that contribute to the speed of the query. On a table with 70,300 rows, the time difference was measured in milliseconds ex: 20 milliseconds vs. 13 milliseconds.

I then looked at the execution plan. There are extra steps in the execution plan for Flapeyre's query. Here's what I think is going on "under the covers". SQL Server is smart enough to recognize that the ordering for a date column is the same as the ordering for a DateTime column. Therefore, it uses the existing index to perform a seek. In this case, since we're talking about a range, SQL Server quickly finds the first row in the index that meets the criteria and quickly finds the last row that meets the criteria. Then SQL Server converts the data within the range to a DATE data type for the final determination about including the row or excluding the row.

With my query, that last step does not need to be done because we are comparing data with the same data type.

Bottom line, my query is slightly faster than Flapeyre's query, but you would need to have millions of rows that match the query criteria to notice a difference in performance.

There are a lot of ways to write the query that would perform much worse than these.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Thanks George. Interesting read. I definitely won't see a difference in this case. The database I'm currently working on now has a little over 1 million records.