Get FirstDate and LastDate of Every month from two Dates

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

  • 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

  • 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.

  • Looks like it has some problems.

  • 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

  • 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

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

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