get last day of month for each month between 2 dates

[scottcabral1979]

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.

 

[imex]

Hi,

Try something like that:

with CTE as
(
    select t.BeginDate, t.EndDate, d.LastDate
    from MyTable as t
    join DateTable as d on d.DateProc = t.BeginDate
    
    union all
    
    select DATEADD(day, 1, c.LastDate), c.EndDate, d.LastDate
    from CTE as c
    join DateTable as d on d.DateProc = DATEADD(day, 1, c.LastDate)
    where c.LastDate < c.EndDate
)

select * from CTE

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[simian336]

Your question was already answered on

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=178870

and you never posted back..

Simi

 

[scottcabral1979]

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.