2 Date Range Queries 2 Differing Results

[NiceArms]

Hey Everyone,

This one has me quite confused.

I have 2 snippets of code which I would expect to return the same results but they are not.

Snippet 1 returns the full range of transaction dates but snippet 2 returns nothing, not a single result.

--Snippet 1
SELECT TransactionDate
,CONVERT(varchar(10), TransactionDate, 103)
,CONVERT(varchar(10), TransactionDate, 102)
FROM dbo.Transactions 
WHERE  CONVERT(varchar(10), TransactionDate, 102) >= '2011.09.30'
AND CONVERT(varchar(10), TransactionDate, 102) <= '2011.10.03'

--Snippet 2
SELECT TransactionDate
,CONVERT(varchar(10), TransactionDate, 103)
,CONVERT(varchar(10), TransactionDate, 102)
FROM dbo.Transactions 
WHERE CONVERT(varchar(10), TransactionDate, 103) >= '30/09/2011'
AND CONVERT(varchar(10), TransactionDate, 103) <= '03/10/2011'

Does anyone have any thoughts on what may be causing this?

/Nice

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[gmmastros]

what do you get when you run this:

Select Data_Type 
From   Information_Schema.Columns 
Where  Table_Name = 'Transactions' 
       And Column_Name = 'TransactionDate'

and this:

select language 
from   sys.syslogins 
where  loginname = suser_sname()

-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

 

[NiceArms]

datetime
and
British

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[markros]

No wonder you're getting different results if you remember character comparison rules.

Anyway, if your Transaction date is a datetime field, then you better avoid any conversions and you dates as your ranges instead of converting dates to characters.

PluralSight Learning Library

 

[NiceArms]

I have used the following many times (on other databases) without issue:

WHERE CONVERT(varchar(10), TransactionDate, 103) >= '30/09/2011'
AND CONVERT(varchar(10), TransactionDate, 103) <= '03/10/2011'

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[gmmastros]

No wonder you're getting different results if you remember character comparison rules.

markros is exactly right. You are converting your date time to a character string and then comparing it to another character string. Snippet 1 works because it is year.month.day so the string comparison mimics date comparison. Snippet 2 doesn't work month/day/year does not mimic string comparisons.

Does this make sense?

Instead, you should be converting the date on the right side of the equal to a date time and comparing data as datetime. Like this:

--Snippet 1
SELECT TransactionDate
,CONVERT(varchar(10), TransactionDate, 103)
,CONVERT(varchar(10), TransactionDate, 102)
FROM dbo.Transactions
WHERE  TransactionDate >= Convert(DateTime, '2011.09.30', 102)
AND TransactionDate <= Convert(DateTime, '2011.10.03', 102)

--Snippet 2
SELECT TransactionDate
,CONVERT(varchar(10), TransactionDate, 103)
,CONVERT(varchar(10), TransactionDate, 102)
FROM dbo.Transactions
WHERE TransactionDate >= Convert(DateTime, '30/09/2011', 103)
AND TransactionDate <= Convert(DateTime, '03/10/2011', 103)

Changing the code the way I suggest will also cause your query to be sargable too. This would allow an existing index to be used to speed up your query.

-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

 

[NiceArms]

It makes 100% sense, thank you.

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[markros]

Just a minor suggestion to George's reply. Instead of converting '30/09/2001' you can always just use universal ISO date format, e.g.
'20110930' and you don't have to use extra convert function (although I don't think it will make any noticeable performance difference).

If you want to learn a bit more about operations with dates, then these two good blogs may help:

The ultimate guide to the datetime datatypes
Bad habits to kick : mis-handling date / range queries


PluralSight Learning Library