Last Years Data (DATEDIFF or DATEADD)

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

Transact-SQL (2000)

Last Years Data (DATEDIFF or DATEADD)

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-10-07 : 16:55:14

I'm trying to pull last years data by using a date function so that I won't have to adjust the query each year. Meaning I can pull the data by using. WHERE ReportingPeriodDate = '2004'. This works but next year I would have to good back and change 2004 to 2005. I'd like it so that when we jump into 2006 the query starts looking for 2005 data.

This is what I tried but all I get is NULL.

WHERE DATEADD (Year, -1, GETDATE()) = DATEPART (YEAR,ReportingPeriodDate)

Any suggestions?

Thanks as always.

GC

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-07 : 17:04:56

Does this work for you?


select	*
from	(select '10/1/2003' ReportingPeriodDate union all
	select '7/11/2004' union all
	select '4/12/2005') a

where	datepart(year, ReportingPeriodDate) = datepart(year, getdate()) - 1

Be One with the Optimizer
TG

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-10-07 : 17:13:11

Yep, That does it!

Many thanks.

GC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-07 : 18:02:34

It is usually better to do a date query as a range selection:
where MyCol >= StartDateTime and MyCol < EndDateTime

You don't have to run each column through a function and the query optimizer can use an index on the date column (if it exists).

For your example, it would be:


where
	ReportingPeriodDate >=
	-- Beginning of last year
	dateadd(year,datediff(year,0,getdate())-1,0)
	and
	ReportingPeriodDate <
	-- Beginning of this year
	dateadd(year,datediff(year,0,getdate()),0)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources