sql server - SQL function for last 12 months

Question

I am looking for a SQL-function that gives the last 12 months with Start Date and End Date. Say you pick 10.Dec, it will give a result in:

 - StartDate   --   EndDate 
 - 2013-11-01  -  2013-11-30
 - 2013-10-01  -  2013-10-31
 - 2013-09-01  -  2013-09-30 

and so it goes for the last 12 months.

I tried modifying an old function we had, but I got totally off and confused in the end.

ALTER FUNCTION [dbo].[Last12Months](@Date date) RETURNS TABLE 
AS  
Return
( 
with cte as (
SELECT DATEADD(mm, DATEDIFF(mm, 01, @Date), 01) AS Start,
       DATEADD(mm, DATEDIFF(mm, -12, @Date), -12) AS EndDate
 union all
select Start - 1, EndDate - 1 from cte
where Start >= @Date )
select CAST(Start as DATE) StartDate, CAST(EndDate as DATE) EndDate from cte)

Runned it like this:

select * from dbo.Last12Months ('2013-12-10')

and got:

 - StartDate   -  EndDate 
 - 2013-12-02  -  2013-12-20

Anyone know what to do?

Answer 1

This seems to do the job - whether you want to put it in a function or just wherever you need to have the data:

; With Numbers as (
    select ROW_NUMBER() OVER (ORDER BY number ) as n
    from master..spt_values
), Months as (
    select DATEADD(month,n,'20010101') as start_date,
           DATEADD(month,n,'20010131') as end_date
    from Numbers
)
select * from Months
where DATEDIFF(month,start_date,GETDATE()) between 0 and 11

(Substitute any other date for GETDATE() if you want to get it based on some other date)

(On my machine, this can generate any month from January 2001 on to at least the next century - it can be adjusted if you need earlier or later dates also)