First Monday of the Month
-
Des,
Works perfectly
Thanks.
-
Des,
Thanks,
Perfect solution.
I've been struggling with this for the last 2 Days.
Regards,
Simon
-
This looks about right, and if it is, then the start and end dates can be calculated.
;WITH SampleDates AS (
SELECT * FROM (VALUES
(GETDATE()+0),
('2019-01-26'),
('2019-01-27'),
('2019-01-28'),
('2019-01-29'),
('2019-01-30'),
('2019-01-31'),
('2019-02-01'),
('2019-02-23'),
('2019-02-24'),
('2019-02-25'),
('2019-02-26'),
('2019-03-23'),
('2019-03-24'),
('2019-03-25')
) d (thedate)
)
SELECT
thedate,
z.RealStartDate,
RealEndDate = DATEADD(DAY,27,z.RealStartDate)
FROM SampleDates d
CROSS APPLY (
SELECT FDOM = DATEADD(month,DATEDIFF(MONTH,0,thedate),0)
) x
CROSS APPLY (
SELECT StartDate = x.FDOM - (DATEDIFF(DAY,0,x.FDOM)%7)
) y
CROSS APPLY (
SELECT RealStartDate = CASE WHEN thedate >= DATEADD(WEEK,4,y.StartDate) THEN DATEADD(WEEK,4,y.StartDate) ELSE y.StartDate END
) z
ORDER BY thedate“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 - Simon Hundleby wrote:
Hi I have a requirement to calculate the start and end of the working Month, The criteria is that if the 1st of the Month is a Monday then use that Date otherwise use the last Monday of the previous month. Example data Below
I want to be able to create a function that will correctly calculate the start and end of the month for any given date, any year. So far I have come up with this quite crude solution . However its not working quite correctly.
The above example should return '2019-01-28' as it's the start of February, How ever It's returning for the start of January. Any help would be much appreciated. Regards, Simon
Hi Simon,
Except for Chris Morris' code (which was posted recently), I've tested all of the code posted so far and, I could be wrong, but only Scott's code even comes close to what you asked for. Still, even that good bit of code has a problem in that if you give it a date of, say, '2019-01-28', it should come up with the date range for February but, instead, comes up with the date range for January. Like I said, good code but it doesn't correctly calculate the leading edge cases for your definition of what the start of a "Working Month" should be.
This question has also come up a whole lot over the years and so I finally took a crack at it with good success. Now... I've done what you asked for but I've seen enough to know that you'll end up trying to herd some cats because people are going to ask for several other things concerning this "Working Month" function and so I added a bunch of stuff. If you don't want that extra stuff, just comment it out so that when people do end up asking for it, it'll be easy to comply.
Here's the fully documented and tested code. I've also included the code to use it in a test from 1900 up to and not including 2100 in the "Usage Example(s) session. I've documented the code as if I were going to put it into production for myself because I forget stuff after a couple of years of not looking at the code 😀
Also, for anyone else reading this and as it says in the "Programmers Notes" in the code, THIS IS NOT A VALID METHOD TO CALCULATE ISO WEEKS. Read the notes. If you need code for ISO Years and Weeks as well as my interpretation (not included in the ISO Standards) for what the definition of ISO Months should be along with a shedload of other columns, I have one ready. I believe I'm going to take the time to publish this one and the ISO function I built (which even works for 2005).
Here's the function that will solve the problem that you posted and has the anti-cat herding columns added.
And, yes, this function handles the leading edge cases, as well. Run the embedded test code and see.
CREATE FUNCTION dbo.WorkingMonthDates
/**********************************************************************************************************************
Purpose:
Given a DATETIME value, return the Start and End dates of the "working month".
-----------------------------------------------------------------------------------------------------------------------
RETURNED COLUMNS:
[DATE]:
A playback of the original date provided to this function.
DoW:
The 3 day English abbreviation for the day of week. (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
YYYYMMM:
The 4 digit year and 3 letter English month of the "Working Month" for the given date. See "Programmers Notes" #3.
MMM:
The 3 letter English month of the "Working Month" for the given date. See "Programmers Notes" #3.
MonthStartDate:
The starting date of the "working month" is defined at Monday of the working week (Mon-Sun) that contains the first
of the month even if that Monday is a part of the previous calendar month. See "Programmers Notes" #3.
MonthEndDate:
The ending date of the "working month" is calculated the same way as the MonthStartDate but for the following month
and then 1 day is subtracted. See "Programmers Notes" #4.
NextMonstStartDate:
This is the starting date of the next month and is included just in case someone wants to work the month date range
comparisons correctly using the tried an true, precision agnostic method of... (See "Programmers Notes" #3.)
WHERE SomeDate >= StartDate AND someDate < NextStartDate.
WeekOfMonth;
This is the week of the month that the given @pSomeDate occurs in. Someone always ends up asking for this.
WeeksInMonth
This is a count of the number of weeks in the working month. Again, someone always ends up asking for this..
-----------------------------------------------------------------------------------------------------------------------
Programmers Notes:
1. NOTE THAT, DESPITE SOME INCREDIBLE SIMULARITIES, THIS IS **NOT** A SOLUTION FOR ISO WEEKS, which stipulate that the
starting week of the year must contain the first Thursday of the year.
2. Dates prior to 1900-01-01 may fail silently and will not return correct results.
3. Since the function calculates "Working Months" based on the week where the first of the month occurs no matter
where in the week it occurs and all weeks must start on Monday, the calendar month MonthStartDate will be in the
previous month unless the first Monday of the month is also the first of the month.
4. Due to the way the MonthStartDates are calculated, the MonthEndDates will always be in both the same calendar
month as the calculated "Working Month".
5. This function uses "Direct Date Math" that's only available in the DATETIME-related datatypes. Microsoft made that
wonderful convenience impossible in the newer datatypes such a DATE and DATETIME2. It's a real tradgecy especilly
when it comes to the DATE datatype because it's only 3 bytes wide and would otherwise be quite useful especially
for something like this function.
-----------------------------------------------------------------------------------------------------------------------
Usage example(s):
--===== Basic Scalar Syntax
SELECT *
FROM dbo.WorkingMonthDates(@pSomeDate [DATETIME])
;
--===== For Testing or to Create a "Working Month" Calendar Table (Indexes not included).
-- This also provides and example of how to play the function against a table or table-like structure.
DECLARE @SomeStartDate DATETIME = '1900' --Inclusive
,@SomeEndDate DATETIME = '2100' --Exclusive
;
WITH cteGenTestDates AS
(
SELECT TOP (DATEDIFF(dd,@SomeStartDate,@SomeEndDate))
SomeDate = @SomeStartDate+ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT wmd.*
FROM cteGenTestDates gtd
CROSS APPLY dbo.WorkingMonthDates(gtd.SomeDate) wmd
ORDER BY MonthStartDate
;
-----------------------------------------------------------------------------------------------------------------------
References:
1. Created to solve the question at the following link:
https://qa.sqlservercentral.com/forums/topic/first-monday-of-the-month
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 01 - 31 May 2019 - Jeff Moden
- Initial creation and unit testing.
**********************************************************************************************************************/
--===== Define the IO for this function.
(@pSomeDT DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
--===== Caclulate and return the date range of the "Working Month" along with the YYYY-MMM representation of the
-- name of the month.
cteParms AS
(
SELECT SomeDT = DATEADD(dd,DATEDIFF(dd,0,@pSomeDT),0) --Strips any time off the input.
)
,cteFirstTry AS
(
SELECT EndDate = DATEADD(dd,DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,-1,SomeDT),0))/7*7,0)-1
,SomeDT
FROM cteParms
)
,cteDateRange AS
(
SELECT --========= If the "First-Try" end date works out to be less than the given date,
-- then the given date is actually a part of the next month and adding 1 to
-- to that end date comes up with the correct start date of the month.
-- Otherwise, we calculate the start date of the month.
StartDate =
CASE
WHEN ft.EndDate < SomeDT
THEN ft.EndDate + 1
ELSE DATEADD(dd,DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,SomeDT),0))/7*7,0)
END
--========= Similar case here.
-- If the "First-Try" end date works out to be less than the given date,
-- then that end date +1 was actually the start date ane we need to calculate
-- a new end date based on end date + 7 (rather than 6 because the first-try end date was less)
-- Otherwise, we just use that first calculated end date.
,EndDate = CASE
WHEN ft.EndDate < SomeDT
THEN DATEADD(dd,DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,-1,ft.EndDate+7),0))/7*7,0)-1
ELSE ft.EndDate
END
,SomeDT
FROM cteFirstTry ft
)
SELECT [Date] = SomeDT
,DoW = LEFT(DATENAME(dw,SomeDT),3)
,YYYYMMM = CONVERT(CHAR(8),DATENAME(yy,EndDate)+'-'+LEFT(DATENAME(mm,EndDate),3))
,MMM = LEFT(DATENAME(mm,EndDate),3)
,MonthStartDate = StartDate
,MonthEndDate = EndDate
,NextMonthStartDate = EndDate + 1 --Just in case someone wants to do it more correctly
,WeekOfMonth = DATEDIFF(dd,StartDate,SomeDT)/7+1
,WeeksInMonth = DATEDIFF(dd,StartDate,EndDate + 1)/7
FROM cteDateRange
;
GO--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 -
p.s.
Here's what the output looks like. The highlighted row is the "leading" edge case that I spoke of in my previous post. As you can see below, the function I posted handles that correctly.
--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 -
Shifting gears, I expanded the test dates on Chris' good code and it seems to handle the "leading" edge cases in the places I looked. He also did it in a much more elegant manner than this ol' brute force dude did (Nice job Chris!). Here's his code with the expansion. Now all we have to do it get him to wrap it in a function and then HE should write the article on this function :D:D:D In case you missed it, Chris, that was a strong hint! Really cool stuff you did with the CROSS APPLYs (time for this ol' dude to study what you did).Crud... see the post below this one. I did find some errors after all.
--===== Test of the function
DECLARE @SomeStartDate DATETIME = '1900' --Inclusive
,@SomeEndDate DATETIME = '2100' --Exclusive
;
WITH SampleDates AS
(
SELECT TOP (DATEDIFF(dd,@SomeStartDate,@SomeEndDate))
TheDate = @SomeStartDate+ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT
thedate,
z.RealStartDate,
RealEndDate = DATEADD(DAY,27,z.RealStartDate)
FROM SampleDates d
CROSS APPLY (
SELECT FDOM = DATEADD(month,DATEDIFF(MONTH,0,thedate),0)
) x
CROSS APPLY (
SELECT StartDate = x.FDOM - (DATEDIFF(DAY,0,x.FDOM)%7)
) y
CROSS APPLY (
SELECT RealStartDate = CASE WHEN thedate >= DATEADD(WEEK,4,y.StartDate) THEN DATEADD(WEEK,4,y.StartDate) ELSE y.StartDate END
) z
ORDER BY thedate--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 -
Ah... crud. Sorry... I have to take back what I said about Chris' code. I noticed that he was using a DATEADD of 27 days in the final result and did some more checking. It causes a "tolerance buildup" which eventually causes errors...
Here's a screen shot of one of the errors. While the Start date for August, 1954 should indeed be July 26th, the end date should have been August 29th and not the 22nd.
Similarly, September 1954 should have started on August 30th but August 30th is still carrying another error that started on August 23rd and the ending date for September should have been September 26th, not the 19th.
Heh... maybe ol' dude brute force methods aren't so bad after all. 😀 All I have to do now is hope I didn't miss something in the testing I did like I did on Chris' code. Back to checking.
--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 -
Whew! Still checking but the same August 1954 output from the function I built lists things as the OP requested.
So does September 1954...
Ok... I feel better now. Now I can sleep. 😀 And, to be sure, 1954 was just where I randomly scrolled to. I've not checked the whole shootin' match but, I did check that I only have 4 and 5 week months. When I initially had an error in my code, that was the perfect test to find it. The September anomaly that appeared in Chris' code would have shown up as a date span of only 1 week (the August 23rd thing).
--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 -
Also, I missed the final post by Des Norton. Tested that in the same manner as I did previously and his code also also seems to work correctly.
--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 -
This should do, short and simple 😉
😎
USE TEEST;
GO
SET NOCOUNT ON;
GO
DECLARE @MYDATE DATE = '20190101';
;WITH TDATES AS
(
SELECT DATEFROMPARTS(
DATEPART(YEAR,@MYDATE)
,DATEPART(MONTH,@MYDATE)
,1) AS XDATE
)
SELECT
TD.XDATE
,DATEADD(DAY,-(DATEDIFF(DAY,0,@MYDATE) % 7),@MYDATE) AS THE_MONDAY
FROM TDATES TD; - Eirikur Eiriksson wrote:
This should do, short and simple 😉 😎
USE TEEST;
GO
SET NOCOUNT ON;
GO
DECLARE @MYDATE DATE = '20190101';
;WITH TDATES AS
(
SELECT DATEFROMPARTS(
DATEPART(YEAR,@MYDATE)
,DATEPART(MONTH,@MYDATE)
,1) AS XDATE
)
SELECT
TD.XDATE
,DATEADD(DAY,-(DATEDIFF(DAY,0,@MYDATE) % 7),@MYDATE) AS THE_MONDAY
FROM TDATES TD;It doesn't work according to what the OP defined in his original post. If you use 2019-06-15, for example, the "Monday Date" should be 2019-05-27, which is the Monday of the week that contains the first of June. Yours comes up with 2019-06-10, which is the Monday of the week that @MYDATE is in.
The question is also asking for the end date of the month for the given date.
--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 - Jeff Moden wrote:Eirikur Eiriksson wrote:
This should do, short and simple 😉 😎
USE TEEST;
GO
SET NOCOUNT ON;
GO
DECLARE @MYDATE DATE = '20190101';
;WITH TDATES AS
(
SELECT DATEFROMPARTS(
DATEPART(YEAR,@MYDATE)
,DATEPART(MONTH,@MYDATE)
,1) AS XDATE
)
SELECT
TD.XDATE
,DATEADD(DAY,-(DATEDIFF(DAY,0,@MYDATE) % 7),@MYDATE) AS THE_MONDAY
FROM TDATES TD;It doesn't work according to what the OP defined in his original post. If you use 2019-06-15, for example, the "Monday Date" should be 2019-05-27, which is the Monday of the week that contains the first of June. Yours comes up with 2019-06-10, which is the Monday of the week that @MYDATE is in. The question is also asking for the end date of the month for the given date.
Thanks Jeff for pointing this out, made a typo in the post 🙁
😎
Here is the correct code:
USE TEEST;
GO
SET NOCOUNT ON;
GO
DECLARE @MYDATE DATE = '20190615';
;WITH TDATES AS
(
SELECT DATEFROMPARTS(
DATEPART(YEAR,@MYDATE)
,DATEPART(MONTH,@MYDATE)
,1) AS XDATE
)
SELECT
TD.XDATE
,DATEADD(DAY,-(DATEDIFF(DAY,0,TD.XDATE) % 7),TD.XDATE) AS THE_MONDAY
FROM TDATES TD; -
Alter Function dbo.WorkingMonthDates (
@inputDate date)
Returns Table With schemabinding
As
Return
With dates
As (
--Select fom = dateadd(day, 1, eomonth(@inputDate, n.Number - 1))
-- , eom = eomonth(@inputDate, n.Number)
Select fom = dateadd(month, datediff(month, 0, @inputDate) + n.Number, 0)
, eom = dateadd(day, -1, dateadd(month, datediff(month, 0, @inputDate) + (n.Number + 1), 0))
From (Values (0), (1)) n(Number)
Where n.Number < 2
)
, startEndDates
As (
Select StartDate = dateadd(day, -datediff(day, 0, d.fom) % 7, d.fom)
, EndDate = dateadd(day, -datediff(day, 6, d.eom) % 7, d.eom)
From dates d
)
Select [Date] = @inputDate
, DoW = left(datename(weekday, @inputDate), 3)
, YYYYMMM = convert(char(8), datename(year, sed.EndDate) + '-' + left(datename(mm, sed.EndDate), 3))
, MMM = left(datename(mm, sed.EndDate), 3)
, MonthStartDate = sed.StartDate
, MonthEndDate = sed.EndDate
, NextMonthStartDate = dateadd(day, 1, sed.EndDate)
, WeekOfMonth = datediff(day, sed.StartDate, @inputDate) / 7 + 1
, WeeksInMonth = datediff(day, sed.StartDate, dateadd(day, 1, sed.EndDate)) / 7
From startEndDates sed
Where @inputDate Between sed.StartDate And sed.EndDate
GoIf you want the function to return a date date type - use the commented out code to generate the first/end of month dates.
If you want to return a full year - this appears to work:
Declare @startDate date = '2019-01-01';
With dates
As (
Select fom = dateadd(day, 1, eomonth(@startDate, n.Number - 1))
, eom = eomonth(@startDate, n.Number)
--Select fom = dateadd(month, datediff(month, 0, @startDate) + n.Number, 0)
-- , eom = dateadd(day, -1, dateadd(month, datediff(month, 0, @startdate) + (n.Number + 1), 0))
From (Select row_number() over(Order By @@spid) - 1 As rn From sys.all_columns ac) n(Number)
Where n.Number < 12
)
Select ShortMonthName = format(d.fom, 'MMM')
, MonthStart = dateadd(day, -datediff(day, 0, d.fom) % 7, d.fom)
, MonthEnd = dateadd(day, -datediff(day, 6, d.eom) % 7, d.eom)
From dates d;Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs -
Finding this a bit amusing, "The criteria is that if the 1st of the Month is a Monday then use that Date otherwise use the last Monday of the previous month" does not reflect that if the day entered is within the same week as the following start of month then it should be counted as the following month and the Monday before would be the desired date.
The contradiction is apparent in the following statement: "The above example should return '2019-01-28' as it's the start of February, How ever It's returning for the start of January."
😎
This is no rocket science, get the date serial difference between the last day of the month and the input day and a simple sliding window type algorithm can decide where the day belongs, that is within next or previous month.
On a side note, incomplete requirement resolution is far more complex than "simple" rocket science 😉
-
Just for the fun of it... 🙂
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),-- 10
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),-- 100
cte_Calendar (dt) AS (
SELECT TOP (365)
CONVERT(DATE, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '20190101'))
FROM
cte_n2 a CROSS JOIN cte_n2 b-- 10,000
)
SELECT
c.dt,
fm.first_of_month,
DATEPART(dw, fm.first_of_month),
dwo.dw_offset,
oa.offset_adj,
dta.days_to_adj,
DATEADD(DAY, -dta.days_to_adj, fm.first_of_month)
FROM
cte_Calendar c
CROSS APPLY ( VALUES (DATEFROMPARTS(YEAR(c.dt), MONTH(c.dt), 1)) ) fm (first_of_month)
CROSS APPLY ( VALUES (DATEPART(dw, fm.first_of_month) % 7 - 2) ) dwo (dw_offset)
CROSS APPLY ( VALUES ((SIGN(dwo.dw_offset) - ABS(SIGN(dwo.dw_offset))) / 2 * -7) ) oa (offset_adj)
CROSS APPLY ( VALUES (dwo.dw_offset + oa.offset_adj) ) dta (days_to_adj);
Viewing 15 posts - 16 through 30 (of 62 total)
You must be logged in to reply to this topic. Login to reply