May 4, 2018 at 12:44 pm
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 ?
May 4, 2018 at 1:38 pm
I am using sql server 2014, I am getting below error.
'eomonth' is not a recognized built-in function name.
May 4, 2018 at 1:59 pm
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".
May 4, 2018 at 3:17 pm
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);
May 4, 2018 at 3:43 pm
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".
May 4, 2018 at 3:43 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 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