SQL function for last 12 months
Solution 1
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
Solution 2
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)
Solution 3
@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
....
Solution 4
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.
Solution 5
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
Related videos on Youtube
Lebowski
Updated on June 04, 2022Comments
-
Lebowski almost 2 years
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?
-
Clockwork-Muse over 10 yearsGiven the way date and time work in SQL Server (and all other RDBMSs, really), you'd be better off storing an 'exclusive' end date.
-
-
Damien_The_Unbeliever over 10 yearsThe problem is, once that CTE hits february, the end date gets clamped to the 28th or 29th and every subsequent end date computed from that value will similarly be 28th or 29th, not 30th or 31st.
-
Lebowski over 10 yearsYes. This is very true. The months has to be with full days :) Thanks anyways!
-
Lebowski over 10 yearsI dont have a table to select from. I want to make a function to be used in my calendar, that gives the 12 last months. These months i will then further use to get out sales information.
-
Lebowski over 10 yearsI need the 12 previous dates from the date i pick in my calendar. So if i pick today, i will get startdate and enddate of all months before December.
-
Clockwork-Muse over 10 yearsEr, why are you converting the dates to
NVARCHAR
? -
Damien_The_Unbeliever over 10 years@user3086563 - As I say below, just substitute the date you want to work from for
GETDATE()
on that last line and it'll generate the previous 11 months (plus the month that the supplied date is in). If it needs to be 12 months before the current month, just change thebetween
to bebetween 1 and 12
-
Lebowski over 10 yearsThis seems to do the job:) Is it possible to get 1 lesser day tho? So we only get the precise month.
-
ajd over 10 yearsHow is that! Do you want the 12 months up to but not including the current month?
-
Lebowski over 10 yearsYea thats what i want. So if i pick todays date i get Nov2012-Nov2013 And i also want from 1st to end of month, and not from 1st to 1st, if you get me brother. Thanks anyways, this helped me alot.
-
Lebowski over 10 yearsi cant get this to run, Msg 156, Level 15, State 1, Procedure Last12Months, Line 2 Incorrect syntax near the keyword 'declare'. Msg 102, Level 15, State 1, Procedure Last12Months, Line 13 Incorrect syntax near ','. I added ALTER function [dbo].[Last12Months] to the beginning. what am i doing wrong sir?
-
Lebowski over 10 yearsPerfect! You are my saviour. Thank you so much :)
-
ajd over 10 years
select start, dateadd(second,-1,dateadd(month, 1, start)) ends from cte where start < @d
gets you 23:59:59 on the last day of the month -
SarjanWebDev about 8 yearsSELECT n , FORMAT(DATEADD(month, DATEDIFF(month, 0, GETDATE()) - Series.n + 1,0), 'yyyy-MM-dd') AS MonthStartDate , FORMAT(DATEADD(dd, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) - Series.n + 2,0)), 'yyyy-MM-dd') AS MonthEndDate FROM (SELECT TOP 50 ROW_NUMBER() OVER (ORDER BY NAME) AS n FROM master.dbo.syscolumns) AS Series