Question about BETWEEN and dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Question about BETWEEN and dates

Author

Topic

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-16 : 14:16:08

Hello, SQL2008R2
Upon reading the thread about using between I execute the following and got the same execution plan for both. Both use an Index Seek.
It was stated that the first would not be Non Sargeable. Where do I look to see the differences?

SELECT * 
FROM MyTable 
WHERE MyDate BETWEEN DATEADD(month,DATEDIFF(month, 0,GETDATE()),0) 
    AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

SELECT * 
FROM MyTable 
WHERE MyDate >= DATEADD(month,DATEDIFF(month, 0,GETDATE()),0) 
    AND MyDate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-16 : 14:32:08

It's good that you are testing it yourself. I think you misunderstood the other comment. You didn't post the other thread reference but I think I saw it. The comment was referring to when a column (in your case [MyDate]) was used within a user defined function as part of the predicate. It wasn't about BETWEEN vs. >= and <=.

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-16 : 15:11:06

Thank you, TG.

I did misunderstand, as I had posted one possible solution and thought it refered to my solution. (I had removed the udf's from my solution though).

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources