Select Between - SQL Server Forums

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)

Select Between

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2013-08-27 : 09:49:09

I am doing a Select but it doesnt seem to work:
Select * from Table1
WHERE DateDiff(day,getdate(), DateofNext) between -90 and 0

This should return all the records which have a DateofNext within the next 90 days from todays date.
Anyone know what I am doing wrong?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-27 : 11:45:24

Check if there are any rows that satisfy the where condition. You can see what hte where condition is - i.e., if any are between -90 and 0 using this:

Select TOP (100) DateDiff(day,getdate(), DateofNext) AS Diff,* from Table1

Also, as a general rule, I prefer to use the <= and >= approach like shown below. It would be faster if there is an index on DateOfNext

Select * from Table1
WHERE 
	DateofNext >= CAST(DATEADD(dd,-90,GETDATE()) AS DATE)
	AND DateofNext <= CAST(GETDATE() AS DATE)

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 13:07:44

Here's more information on why James K has good advice regarding the change in approach:
[url]http://en.wikipedia.org/wiki/Sargable[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 02:51:58

I've blogged about different approaches for daterange comparison here

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

macca
Posting Yak Master

146 Posts

Posted - 2013-08-28 : 04:49:11

Thanks for all the help guys.
These all helped in one way or another.

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 05:15:40

Please check this

SELECT * FROM Table1
WHERE DateofNext BETWEEN DATEADD(DD,90,GETDATE()) AND GETDATE()

P.Kameswara rao

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 05:19:23

declare @date datetime

select @date=datediff(d,0,DATEADD(d,90,getdate()))

Select * from Table1
WHERE DateofNext between getdate() and @date