How to find First monday of last month in SQL Help

  • I need to get firstMonday of each month. if I pass date in May i should get April 2nd, if I pass date in June I should get May 7th, if I pass date in July I should get June 4th. Can you please help achieving this ?

  • This should work:


    declare @dt date = '2018-05-10'
    select dateadd (day, (9 - datepart(dw, eomonth(@dt, -2)))%7, eomonth(@dt, -2))

    Tested on a small sample,.

  • I am using sql server 2014, I am getting below error.

    'eomonth' is not a recognized built-in function name.


  • SELECT
        passed_date, DATEADD(DAY, -DATEDIFF(DAY, 0, day7) % 7, day7) AS calcd_date
    FROM (
        VALUES(CAST('20180522' AS date)),('20180603'),('20180704')
    ) AS test_data(passed_date)
    CROSS APPLY (
        SELECT DATEADD(DAY, 6, DATEADD(MONTH, DATEDIFF(MONTH, 0, passed_date) - 1, 0)) AS day7
    ) AS calc1

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Here's another solution:
    DROP FUNCTION IF EXISTS dbo.FirstMonday
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:  Joe Torre
    -- Create date: 5/4/2018
    -- Description: Returns the first Monday in the month before the date passed in
    -- SELECT Date, DateName(dw, Date) DayOfTheWeek from dbo.FirstMonday('20180504');
    -- =============================================
    CREATE FUNCTION dbo.FirstMonday (@Date date)
    RETURNS TABLE
    AS
    RETURN
    (
     WITH
          nums (n) AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6))t(n)),
          dates (d) AS (SELECT DateAdd(dd, nums.n, DateAdd(mm,-1, DateAdd(mm, DateDiff(mm, 0, @Date), 0))) FROM nums)
       SELECT d Date
       FROM dates
       WHERE DatePart(dw, d)=2
    )
    GO
    DECLARE @dates table(d date);
    INSERT @dates ( d )
    VALUES ( '20180115' )
         , ( '20180215' )
         , ( '20180315' )
         , ( '20180415' )
         , ( '20180515' )
         , ( '20180615' )
         , ( '20180715' )
         , ( '20180815' )
         , ( '20180915' )
         , ( '20181015' )
         , ( '20181115' )
         , ( '20181215' );
    SELECT Date, DateName(dw, Date) DayOfTheWeek
    FROM
       @dates d
    CROSS APPLY
       dbo.FirstMonday(d);


  • I strongly prefer to avoid code that requires a specific DATEFIRST setting; my code works the same under any setting.
    But this:
    WHERE DatePart(dw, d)=2
    adds a dependency on the DATEFIRST setting.  For the record, which DATEFIRST setting is required for your code to work correctly?
    That said, it's still vastly better than using a bloated calendar table 🙂.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Just in case you need to deal with dates prior to 1900-01-01 but no earlier that 1753-01-01... (-53690 is the integer date serial number for 1753-01-01)

    SELECT DATEADD(dd,DATEDIFF(dd,-53690,(DATEADD(mm,DATEDIFF(mm,-53690,SomePassedDateTime)-1,-53690))+6)/7*7,-53690)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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