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 calc1SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
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)
Intro to Tally Tables and Functions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply