Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

2 Date Range Queries 2 Differing Results

Status
Not open for further replies.

NiceArms

Programmer
May 21, 2009
105
GB
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.

Code:
--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!
 
what do you get when you run this:

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

and this:

Code:
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
 
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!
 
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
 
I have used the following many times (on other databases) without issue:

Code:
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!
 
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:

Code:
--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
 
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!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top