Calculating prior year

[morechocolate]

I have been playing around for a while with the following

select dateadd(year,-1,(dateadd(day, -(datepart(day,somedate-1)), (dateadd(day,datediff(day,0,somedate),0)))))

which returns the last day of the previous month.

I need to return the previous year. For example if somedate is 12/31/2004, I want to return 12/31/2003. If I change the first day to year I get 12/01/2003

Thanks in advance for the help

 

[mrdenny]

Returning the last day of the previous year is easier than it looks.

declare @date datetime
set @date = '8/17/2004'
select '12/31/' + datepart(yy, dateadd(yy, -1, @date))

The last day of the year is always 12/31/XX. The only one you need to figure out is what last year was.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[r937]

for really slick techniques, see vongrunt's faq183-5842

see point #6 for "last day of ..."

r937.com | rudy.ca

 

[amrita418]

To get last day of previous month:

select dateadd(day, -day(getdate()), getdate())

To get the previous year you can just do a datediff with year parameter:

select dateadd(year, -1, getdate())

Regards,
AA

 

[morechocolate]

Thanks everyone for your response. Yes, I agree mrdenny, it is much easier than it looks. Funny how we can sometimes make the easiest things so complicated.