Problem with dates and the new year

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

Problem with dates and the new year

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-10 : 12:45:01

I have a query that has the following where statement:


	o.date>= DATEADD(YEAR, - 1, DATEADD(MONTH, @month - 1, DATEADD(YEAR, @Year - 1900, 0))) AND
	o.date< DATEADD(MONTH, @month, DATEADD(YEAR, @Year - 1900, 0)) AND
	o.date>=dateadd(month,datediff(month,0,getdate())-12,0) AND 
	o.date<dateadd(month,datediff(month,0,getdate())+1,0)

The purpose of this is to pull 1 years worth of data from the current @month and @year supplied. It's worked great, but now that January, 2011 exists as a date everytime this query is pulled it ends up taking all the january figures for 2010 and listing them as January 2011.

I'm not sure how to make this happen.

Craig Greenwood

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 12:49:59

i think its because you're pulling from last day of previous month onwards ( >= in first condition)

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-10 : 13:36:59

It's pulling data from greater than or equal to 2010-01-01 00:00:00.000 to less than 2011-02-01 00:00:00.000. If that is not what you want, then you need to adjust your calculations.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources