CODEWITHSUNDEEP
sqlsql-serverfunctiondate
Lebowski
Lebowski

Reputation: 121

SQL function for last 12 months

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?

Upvotes: 2

Views: 17691

Answers (6)

SarjanWebDev
SarjanWebDev

Reputation: 533

@Lebowski Below script will give you start and end date of specified calendar months from today in chronological order

DECLARE @nMonths TINYINT 
SET @nMonths = 60
SELECT FORMAT(DATEADD(month, n.n - @nMonths+1+ DATEDIFF(month, 0, GETDATE()) -1 ,0), 'yyyy-MM-dd')               AS MonthStartDate
,      FORMAT(DATEADD(dd, -1, DATEADD(month, n.n - @nMonths+1 + DATEDIFF(month, 0, GETDATE()),0)), 'yyyy-MM-dd') AS MonthEndDate
FROM (SELECT TOP(@nMonths) n = ROW_NUMBER() OVER (ORDER BY NAME)
FROM master.dbo.syscolumns) n

Sample output

MonthStartDate  MonthEndDate
2011-04-01  2011-04-30
2011-05-01  2011-05-31
2011-06-01  2011-06-30
2011-07-01  2011-07-31
2011-08-01  2011-08-31
2011-09-01  2011-09-30
2011-10-01  2011-10-31
2011-11-01  2011-11-30
2011-12-01  2011-12-31
....

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

check this,

Declare @i date='2013-12-10'

;with cte as

(Select dateadd(month,datediff(month,0,@i)-1,0) StartDate
,dateadd(day,-1,dateadd(month,datediff(month,0,@i),0)) EndDate ,1 rownum

Union all

select dateadd(month,-1,StartDate),dateadd(day,-1,StartDate),rownum+1 rownum from cte where rownum<12 )

select * from cte

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try using CTE:

ALTER FUNCTION [dbo].[Last12Months]
(
    @Date datetime
)   RETURNS @tbl TABLE (Start datetime, EndDate datetime)
AS   
BEGIN
    WITH T AS(
    SELECT 
        DATEADD(month, DATEDIFF(month, 0, @Date), 0) AS Start,
        DATEADD(d, -DAY(DATEADD(m,1,@date)),DATEADD(m,1,@date)) AS EndDate,
        12 Cnt
    UNION ALL
    SELECT 
        DATEADD(month, -1, Start),
        DATEADD(d, -DAY(DATEADD(m,1,Start-1)),DATEADD(m,1,Start-1)),
        Cnt-1
    FROM
        T
    WHERE
        Cnt-1>0
    )
    INSERT INTO @tbl 
        (Start, EndDate)
    SELECT 
        Start, EndDate
    FROM T

    RETURN
END

Upvotes: 2

ajd
ajd

Reputation: 523

Damn, you've got to be quick on SO!

Good use of CTEs: i've learnt a bit answering this...

alter function Last12Months(@d date) returns table
as
return(
with cte as (
    select 
        dateadd(month, datepart(mm,@d)-13, 
            dateadd(year,datepart(yyyy,@d)-1900,0)
            )
        as start
    union all
      select dateadd(mm, 1, start) from cte
      where start < @d)
select start, dateadd(mm, 1, start) ends from cte
    where start < @d
)
go

select * from  Last12Months('2014-06-04')

Removed conversion to varchar thanks to Date serial in SQL?

This returns 13 months: from say June last year to this June, inclusive.

To return the previous 12 months, not including the current June, change the final start<@d to

where start < dateadd(month, datepart(mm,@d)-1, 
    dateadd(year,datepart(yyyy,@d)-1900,0))

The end is 00:00 hours on the first day of the next month.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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)

Upvotes: 2

Jade
Jade

Reputation: 2992

Try this it might help you

select top 12 *
from YourTable
where dateOf between @DateFrom and @DateTo
order by dateOf  desc

Upvotes: 0

Related Questions