Using between for dates using getdate

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)

Using between for dates using getdate

Author

Topic

mlawton
Starting Member

35 Posts

Posted - 2011-12-07 : 12:13:35

Hi:

I am trying to do the following:

I have a table with all 2010 data.
I want to select 2010 data based on the getdate between +3 days and -3 days.

This is what I have so far:
SELECT *
FROM PriorYR_Data
WHERE datepart(dd,date) = datepart(dd,CONVERT(VARCHAR(11),GETDATE()-3,101))
and datepart(mm,date) = datepart(mm,CONVERT(VARCHAR(11),GETDATE()-3,101))
and datepart(yyyy,date) = datepart(yyyy,CONVERT(VARCHAR(11),GETDATE(),101))-1

SELECT *
FROM PriorYR_Data
WHERE datepart(dd,date) = datepart(dd,CONVERT(VARCHAR(11),GETDATE()+3,101))
and datepart(mm,date) = datepart(mm,CONVERT(VARCHAR(11),GETDATE()+3,101))
and datepart(yyyy,date) = datepart(yyyy,CONVERT(VARCHAR(11),GETDATE(),101))-1

How can I get all the dates do showup
12/10/2010
12/9/2010
12/8/2010
12/7/2010
12/6/2010
12/5/2010
12/4/2010
instead of just 12/4/2010 and 12/10/2010?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 12:24:58

i think you need only this


....
SELECT *
FROM PriorYR_Data
WHERE date>= DATEADD(yy,-1,GETDATE()-3)
AND date< DATEADD(yy,-1,GETDATE()+4)
.....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2011-12-07 : 12:32:16

Thank you!!!

That worked perfectly!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 12:34:28

see this too

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources