Correct "GetDate()-14" statement

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 2005 Forums

Transact-SQL (2005)

Correct "GetDate()-14" statement

Author

Topic

crownclit
Starting Member

20 Posts

Posted - 2007-08-12 : 21:00:30

How do I construct proper WHERE clause to get records between 14 and 8 days ago
I tried following but it does not work.
(Dbo.From.Date Between GetDate()-14 and Getdate()-8)

marat
Yak Posting Veteran

85 Posts

Posted - 2007-08-12 : 21:51:41

where datediff(day, Dbo.From.Date, getdate()) between 8 and 14

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-12 : 22:19:40

quote:
Originally posted by crownclit

How do I construct proper WHERE clause to get records between 14 and 8 days ago
I tried following but it does not work.
(Dbo.From.Date Between GetDate()-14 and Getdate()-8)

What do you mean it does not work ? Syntax Error ? Did not select the desired result ? Can you explain further ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-12 : 22:35:51

This will remove the time component of the getdate()


where Dbo.From.Date between dateadd(day, datediff(day, 0, getdate()), -14)
	            and	    dateadd(day, datediff(day, 0, getdate()), -8)

KH
[spoiler]Time is always against us[/spoiler]

crownclit
Starting Member

20 Posts

Posted - 2007-08-12 : 22:44:22

thanks Marat all works fine now

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-12 : 22:48:53

check out the performance of both solutions posted.

KH
[spoiler]Time is always against us[/spoiler]

marat
Yak Posting Veteran

85 Posts

Posted - 2007-08-12 : 23:35:46

quote:
Originally posted by crownclit

thanks Marat all works fine now

I think your problem was with the number of returned rows;
check this out:
use AdventureWorks
go
SET STATISTICS TIME ON
GO
select DueDate from Purchasing.PurchaseOrderDetail
where Purchasing.PurchaseOrderDetail.DueDate
Between Getdate()-1156 -1 and GetDate()-1150
--returns the same set of data as
select DueDate from Purchasing.PurchaseOrderDetail
where datediff(day, Purchasing.PurchaseOrderDetail.DueDate, Getdate()) between 1150 and 1156

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-13 : 01:57:57

www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources