retrieve date - [RESOLVED] - SQL Server Forums

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

Transact-SQL (2008)

retrieve date - [RESOLVED]

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-24 : 12:25:04

How to I create a result set that returns all months from the past 12 months from the last day of the last month?

2012-04
2012-03
2012-02
...
2011-05

Thanks for your help.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2012-05-24 : 14:11:13

This should do it..

declare @date datetime
set @date = dateadd(mm,datediff(mm,0,getdate()),-1) --last date of last month

select convert(char(7), dateadd(mm, -number, @date), 20)
from 
    (select top 12 number from master.dbo.spt_values
	where name is null
	) t

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:52:55

[code]
declare @date datetime
set @date = dateadd(mm,datediff(mm,0,getdate()),-1)

;with prev_dates (dt)
AS
(
select @date
union all
select dateadd(mm,-1,dt)
from prev_dates
where dateadd(mm,-1,dt) >= dateadd(mm,datediff(mm,0,getdate())-12,-1)
)

select datename(yy,dt) + '-' + datename(mm,dt)
from prev_dates

[/code]

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

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-24 : 16:18:12

Great - Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources