Get last day of month for each month btwn 2 dates

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)

Get last day of month for each month btwn 2 dates

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2012-09-14 : 15:25:49

Hi,

I'm trying to write some code that will get me the last day of each month for the months that fall between 2 dates.

So if i have a table with a record with a begin date of 01-01-2012 and an end date of 09-14-2012, i would want a result set of the following 9 records:

01-31-2012
02-29-2012
03-31-2012
04-30-2012
05-31-2012
06-30-2012
07-31-2012
08-31-2012
09-30-2012

So i basically get the last day of each month between the dates listed, including the months that the dates are in themselves (inclusive list).

I have a date dimension table with 1 row for every day from 1990 to 2025. There is also a field with the Last day of Month for a given date. My issue is that I'm not sure how to write the T-SQL to get what I need.

any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 16:05:49

its not between as you've 09-30-2012 which comes after end date value

anyways here you go


DECLARE @StartDate date='20120101',@EndDate date ='20120914'
;With Dates (dateVal)
AS(
SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate)+1,0)-1
UNION ALL
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,dateVal))+1,0)-1
FROM Dates
WHERE DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,dateVal))+1,0)-1<=DATEADD(mm,DATEDIFF(mm,0,@EndDate)+1,0)-1
)

SELECT *
FROM Dates

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

Mike Jackson
Starting Member

37 Posts

Posted - 2012-09-14 : 16:24:52

add a field to your calender table called ldotm type bit

Update dbo.calendar
set ldotm=1
where date in (SELECT DATEADD(dd, -DAY(DATEADD(m,1,caldate)), DATEADD(m,1,caldate))
from dbo.Calendar
where day(caldate)=1)

DECLARE @StartDate datetime
declare @EndDate datetime
set @StartDate='20120101'
set @EndDate= '20120914'

select caldate
from dbo.calendar
where caldate >= @StartDate and caldate <= @EndDate
and ldotm =1

scabral7
Yak Posting Veteran

57 Posts

Posted - 2012-09-17 : 21:30:06

Thanks,

I ended up using the follwoing logic:

CREATE TABLE #scabral7 (
ID BIGINT
, Effective DATETIME
, Expiration DATETIME
)

INSERT INTO #scabral7 (
ID, Effective, Expiration)
SELECT 123, '2012-01-01', '2012-09-30'

SELECT ID, e, Effective, Expiration
FROM #scabral7 AS a
INNER JOIN (SELECT
DateAdd(m, number, '1990-01-01') AS b
, DateAdd(m, number, '1990-01-31') AS e
FROM master.dbo.spt_values
WHERE 'P' = type) AS b
ON (b.b BETWEEN DateAdd(m, DateDiff(m, 0, a.Effective), 0) AND a.Expiration

This returns a row for each ID for each month that the row is effective.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources