How to get date of a beginning of first work weekday even if the Monday is from last month?

Question

My goal to is get get query that will return weekdays in a month. I can get the days of the month but I need to get dates starting from monday through Friday even if the Monday may be in the preceding month. Example April 1st is a wednesday so I would need to bring back March 30th and 31st. And the last date returned would be by May 1st as that is the last friday that contains some April days..

Is this part of an application? This would be easier to achieve with code rather than an SQL query. — silkfire, Mar 04 '20 at 13:57
SQL could achieve this just as easily, @silkfire . The key here would be the OP having a Calendar table. — Thom A, Mar 04 '20 at 14:01
Unfortunately it will be part of a stored procedure being as dataset for a SSRS report. It will fill in employees schedule for current month and next following 2 months — Tom, Mar 04 '20 at 14:01
Alright, I am of the belief that SQL is for storage, not for convoluted programming logic :) — silkfire, Mar 04 '20 at 14:05
Try this link https://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008 — JonWay, Mar 04 '20 at 14:18
@silkfire I agree that sql is not for convoluted programming logic, but a calendar table is not convoluted or programming logic. It is actually storing dates along with properties for those dates. — Sean Lange, Mar 04 '20 at 14:19

score 1 · Answer 1 · answered Mar 04 '20 at 14:24

If interested in a helper function, I have TVF which generates a calendar.

Example

Select * from [dbo].[tvf-Date-Calendar-Wide]('2020-04-01')

Returns

So, with a little tweak, we get can

Select WeekNr = RowNr
      ,B.*
 From  [dbo].[tvf-Date-Calendar-Wide]('2020-04-01') A
 Cross Apply ( values (Mon)
                     ,(Tue)
                     ,(Wed)
                     ,(Thu)
                     ,(Fri)
             ) B(Date)

Which Returns

WeekNr  Date
1       2020-03-30
1       2020-03-31
1       2020-04-01
1       2020-04-02
1       2020-04-03
2       2020-04-06
2       2020-04-07
2       2020-04-08
...
5       2020-04-29
5       2020-04-30
5       2020-05-01

The Function If Interested

CREATE FUNCTION [dbo].[tvf-Date-Calendar-Wide] (@Date1 Date)
Returns Table
Return (

Select RowNr,[Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat]
 From  (
        Select D
              ,DOW=left(datename(WEEKDAY,d),3)
              ,RowNr = sum(Flg) over (order by D)
         From (
                Select D,Flg=case when datename(WEEKDAY,d)= 'Sunday' then 1 else 0 end
                 From (Select Top (42) D=DateAdd(DAY,-7+Row_Number() Over (Order By (Select Null)),@Date1) From  master..spt_values n1 ) A
              ) A
       ) src
 Pivot (max(d) for DOW in ([Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat]) )pvg
 Where [Sun] is not null
   and [Sat] is not null
 )
-- Select * from [dbo].[tvf-Date-Calendar-Wide]('2020-04-01')

GarethD · Answer 2 · 2020-03-04T18:31:11.873

You first need to find the start of the week for the first day of the month, then the date for the end of the week that contains the last day of the month:

e.g.

SELECT  WeekStart = DATEADD(DAY, -(DATEPART(WEEKDAY, '20200401')-1), '20200401'),
        WeekEnd = DATEADD(DAY, 7-(DATEPART(WEEKDAY, '20200430')), '20200430');

Gives:

WeekStart       WeekEnd
------------------------------
2020-03-29      2020-05-02

You wouldn't want to hard code the first and the last of the month, but these are fairly trivial things to get from a date:

DECLARE @Date DATE = '20200415';

SELECT  MonthStart = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0),
        MonthEnd = EOMONTH(@Date);

Which returns

MonthStart      MonthEnd
------------------------------
2020-04-01      2020-04-30

You can then just substitute this into the first query for week starts:

DECLARE @Date DATE = '20200401';

SELECT  WeekStart = DATEADD(DAY, -(DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0))-1), DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)),
        WeekEnd = DATEADD(DAY, 7-(DATEPART(WEEKDAY, EOMONTH(@Date))), EOMONTH(@Date));

Which gives the same output as the first query with hard coded dates. This is very clunky though, so I would separate this out into a further step:

DECLARE @Date DATE = '20200401';

-- SET DATE TO THE FIRST OF THE MONTH IN CASE IT IS NOT ALREADY
SET @Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0);

SELECT  WeekStart = DATEADD(DAY, -(DATEPART(WEEKDAY, @Date)-1), @Date),
        Weekend = DATEADD(DAY, 7-(DATEPART(WEEKDAY, EOMONTH(@Date))), EOMONTH(@Date));

Again, this gives the same output (2020-03-29 & 2020-05-02).

The next step is to fill in all the dates between that are weekdays. If you have a calendar table this is very simple

DECLARE @Date DATE = '20200415';

-- SET DATE TO THE FIRST OF THE MONTH IN CASE IT IS NOT ALREADY
SET @Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0);

DECLARE @Start DATE = DATEADD(DAY, -(DATEPART(WEEKDAY, @Date)-1), @Date),
        @End DATE = DATEADD(DAY, 7-(DATEPART(WEEKDAY, EOMONTH(@Date))), EOMONTH(@Date));

SELECT  [Date], DayName = DATENAME(WEEKDAY, [Date])
FROM    Calendar
WHERE   Date >= @Start
AND     Date <= @End
AND     IsWeekday = 1
ORDER BY [Date];

If you don't have a calendar table, then I suggest you create one, but if you can't create one you can still generate this on the fly, by generating a set series numbers and adding these numbers to your start date:

DECLARE @Date DATE = '20200415';

-- SET DATE TO THE FIRST OF THE MONTH IN CASE IT IS NOT ALREADY
SET @Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0);

DECLARE @Start DATE = DATEADD(DAY, -(DATEPART(WEEKDAY, @Date)-1), @Date),
        @End DATE = DATEADD(DAY, 7-(DATEPART(WEEKDAY, EOMONTH(@Date))), EOMONTH(@Date));

-- GET NUMBERS FROM 0 - 50
WITH Dates (Date) AS
(   SELECT  TOP (DATEDIFF(DAY, @Start, @End)) 
            DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY n1.n) - 1, @Start)
    FROM    (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n1 (n)
            CROSS JOIN (VALUES (1),(1),(1),(1),(1)) n2 (n)
)
SELECT  [Date], DayName = DATENAME(WEEKDAY, [Date])
FROM    Dates
WHERE   ((DATEPART(WEEKDAY, [Date]) + @@DATEFIRST) % 7) NOT IN (0, 1);

score 0 · Answer 3 · answered Mar 04 '20 at 14:14

Just generate all possible dates -- up to 6 days before the month begins. Take the valid weekdays after the first Monday:

with dates as (
      select dateadd(day, -6, convert(date, '2020-04-01')) as dte
      union all
      select dateadd(day, 1, dte)
      from dates
      where dte < '2020-04-30'
     )
select dte
from (select d.*,
             min(case when datename(weekday, dte) = 'Monday' then dte end) over () as first_monday
      from dates d
     ) d
where datename(weekday, dte) not in ('Saturday', 'Sunday') and
      dte >= first_monday;

score 0 · Answer 4 · answered Mar 04 '20 at 15:44

declare @dateVal datetime = GETDATE(); --assign your date here

declare @monthFirstDate datetime = cast(YEAR(@dateVal) as varchar(4)) + '-' + DATENAME(mm, @dateVal) + '-' + cast(01 as varchar(2))
declare @monthLastDate datetime = DAteADD(day, -1, DATEADD(month, 1, cast(YEAR(@dateVal) as varchar(4)) + '-' + DATENAME(mm, @dateVal) + '-' + cast(01 as varchar(2))))

declare @startDate datetime = DATEADD(DAY, 2 - DATEPART(WEEKDAY, @monthFirstDate), CAST(@monthFirstDate AS DATE)) 
declare @enddate datetime = DATEADD(DAY, 6 - DATEPART(WEEKDAY, @monthLastDate), CAST(@monthLastDate AS DATE)) 

Select @startDate StartDate, @enddate EndDate

****Result**
--------------------------------------------------------------
    StartDate                |   EndDate
-----------------------------|--------------------------------
    2020-03-02 00:00:00.000  |  2020-04-03 00:00:00.000
-----------------------------|---------------------------------**

How to get date of a beginning of first work weekday even if the Monday is from last month?

4 Answers4