New Year Can't get the right Dates

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

SQL Server Development (2000)

New Year Can't get the right Dates

Author

Topic

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2006-01-03 : 14:07:48

I have a query that pulls data for the current month and last month. With the new year comes a problem with my query. I am using month(getdate())-1 to pull last months data but with the year now being 2006 it returns NULL or zero instead of December 2005. Any ideas how to fix this so the query will pull December 2005 for last month and future new years? I think this will only be a problem every January.

Thanks,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 14:18:33

Not sure what criterial you want but here is a where clause that will get all [myDate] values from previous month:

select	myDate
from	(select getdate() myDate union 
	select dateAdd(month, -1, getdate())) a
where	myDate >= dateadd(month, datediff(month,0,getdate())-1, 0)
and	myDate < dateadd(month, datediff(month,0,getdate()), 0)

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-03 : 14:31:26

This will give you the data for the current and prior month, and will work all year:

select
	MyDate
from
	MyTable a
where
	MyDate >= dateadd(mm,datediff(mm,0,getdate())-1,0)	and
	MyDate <  dateadd(mm,datediff(mm,0,getdate())+1,0)

CODO ERGO SUM

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2006-01-03 : 16:02:11

Thanks everyone for the help. Dateadd function was the way to go. Application is working now.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 00:25:26

Also refer this
http://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