Get FirstDate and LastDate of Every month from two Dates

Question

Post reply

Get FirstDate and LastDate of Every month from two Dates

bhavin.gala05

Valued Member

Points: 72
More actions
November 7, 2015 at 11:47 am

#306418

Comments posted to this topic are about the item Get FirstDate and LastDate of Every month from two Dates

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Why write a slow unwieldy multi-statement table-valued function when an inline table-valued function will do?

DECLARE

@StartDate DATE = '05/02/2015',

@EndDate DATE = '11/25/2015'

SELECT

ID = n+1,

FirstDate = DATEADD(MONTH,n, StartPoint),

LastDate = DATEADD(DAY,-1,DATEADD(MONTH,n+1, StartPoint)),

[Month] = MONTH(DATEADD(MONTH,n, StartPoint)),

[Year] = YEAR(DATEADD(MONTH,n, StartPoint))

FROM (

SELECT TOP(1+DATEDIFF(MONTH, @StartDate, @EndDate))

n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,

StartPoint = CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,@StartDate),0) AS DATE)

FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n)

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

roban711 SSC Rookie Points: 25 More actions · Answer 2

the GetFLdatelist function does not take into account for the month of Feburary. If you use Feb as a start date the results look like this:

12015-02-01 00:00:00.0002015-02-28 00:00:00.00022015

22015-03-01 00:00:00.0002015-03-28 00:00:00.00032015

32015-04-01 00:00:00.0002015-04-28 00:00:00.00042015

42015-05-01 00:00:00.0002015-05-28 00:00:00.00052015

52015-06-01 00:00:00.0002015-06-28 00:00:00.00062015

62015-07-01 00:00:00.0002015-07-28 00:00:00.00072015

72015-08-01 00:00:00.0002015-08-28 00:00:00.00082015

82015-09-01 00:00:00.0002015-09-28 00:00:00.00092015

92015-10-01 00:00:00.0002015-10-28 00:00:00.000102015

102015-11-01 00:00:00.0002015-11-28 00:00:00.000112015

Notice the end dates are on the 28th vs. 30th or 31st.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

akljfhnlaflkj

SSC Guru

Points: 76202

November 9, 2015 at 1:00 pm

#1838668

Looks like it has some problems.

David Fundakowski Ten Centuries Points: 1328 More actions · Answer 4

I would go with a simple recursive statement.

DECLARE @dateone AS DATE = '2012-01-01'
DECLARE @datetwo AS DATE = '2015-04-12'
;WITH cte AS (
SELECT 1 AS month_
UNION ALL
SELECT 1+ month_
FROM cte
WHERE month_ <= DATEDIFF(MONTH, @dateone, @datetwo)
)
select *
, DATEADD(DAY, 1, EOMONTH(@dateone, month_-2)) AS first_of_month
, EOMONTH(@dateone, month_-1)
from cte

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

david.fundakowski (11/9/2015)
I would go with a simple recursive statement.
DECLARE @dateone AS DATE = '2012-01-01'
DECLARE @datetwo AS DATE = '2015-04-12'
;WITH cte AS (
SELECT 1 AS month_
UNION ALL
SELECT 1+ month_
FROM cte
WHERE month_ <= DATEDIFF(MONTH, @dateone, @datetwo)
)
select *
, DATEADD(DAY, 1, EOMONTH(@dateone, month_-2)) AS first_of_month
, EOMONTH(@dateone, month_-1)
from cte

I didn't know you could do that with EOMONTH(), so thanks!

rCTE's are a very expensive way to generate rows:

DECLARE @dateone AS DATE = '1815-01-01';

DECLARE @datetwo AS DATE = '2015-04-12';

DECLARE @NOW DATETIME = GETDATE();

;WITH cte AS (

SELECT 1 AS month_

UNION ALL

SELECT 1+ month_

FROM cte

WHERE month_ <= DATEDIFF(MONTH, @dateone, @datetwo)

)

SELECT ID = month_

, DATEADD(DAY, 1, EOMONTH(@dateone, month_-2)) AS FirstDate

, EOMONTH(@dateone, month_-1) AS LastDate

, MONTH(EOMONTH(@dateone, month_-1)) AS [Month]

, YEAR(EOMONTH(@dateone, month_-1)) AS [Year]

FROM cte

OPTION (MAXRECURSION 0);

SELECT DATEDIFF(MILLISECOND,@NOW,GETDATE());

----------------------------------------------------------------------------------------------------------

DECLARE

@StartDate DATE = @dateone,

@EndDate DATE = @datetwo;

SET @NOW = GETDATE();

SELECT

ID = n,

FirstDate = DATEADD(DAY, 1, EOMONTH(@dateone, n-2)),

LastDate = EOMONTH(@dateone, n-1),

[Month] = MONTH(EOMONTH(@dateone, n-1)),

[Year] = YEAR(EOMONTH(@dateone, n-1))

FROM (

SELECT TOP(1+DATEDIFF(MONTH, @StartDate, @EndDate))

n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)

) d;

SELECT DATEDIFF(MILLISECOND,@NOW,GETDATE());

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden