dynamic Year and date formula needed

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

General SQL Server Forums

New to SQL Server Programming

dynamic Year and date formula needed

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2008-08-28 : 12:55:29

I need to pull all dates out of a field dynamically. Using today's dates as an example - I need all records with dates from July - Oct. Below is my formula (it works):

WHERE thru_date < = dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1
and thru_date > = dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1

I also need the same formula to include all days in this same period of the next year. For today i need everyone who has the dates between July 2009 - Oct 2009.

How can I add the year criteria to the formula?

Thanks, dz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 12:59:38

add a year also

WHERE thru_date < = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1)
and thru_date > = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1)

dzabor
Posting Yak Master

138 Posts

Posted - 2008-08-28 : 13:02:53

That worked - Thanks!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 03:07:31

quote:
Originally posted by visakh16

add a year also

WHERE thru_date < = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1)
and thru_date > = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1)

If you want to include from 1st of July, 2009

WHERE thru_date < = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1)
and thru_date > dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate())),0)-1)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 03:08:43

More accurately

WHERE thru_date < dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0))
and thru_date > dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate())),0)-1)

Madhivanan

Failing to plan is Planning to fail

dzabor
Posting Yak Master

138 Posts

Posted - 2008-08-29 : 05:47:12

Thanks - that is good to know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources