November 7, 2015 at 11:47 am
Comments posted to this topic are about the item Get FirstDate and LastDate of Every month from two Dates
November 9, 2015 at 2:47 am
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
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
November 9, 2015 at 6:22 am
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.
November 9, 2015 at 1:00 pm
Looks like it has some problems.
November 9, 2015 at 1:39 pm
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
November 10, 2015 at 1:57 am
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());
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy