Finding the last X day of the month isn’t as easy as you might think. I learned this when people wanted to build a calendar for planning and automation.

This is a neat addition to the article: Dates In SQL Server: Weekdays & Weekends

Create sample date data

From my previous article, the below SQL will create a date range collection. I’ve picked a date range where we have some months with Friday being the last weekday of the week and some where it isn’t:

--Set the start of the week to Monday
SET DATEFIRST 1;

--------------------------------------------------------------------------------
-- Create Date Table
--------------------------------------------------------------------------------
CREATE TABLE date_t
(
  [TheDate]   DATE NOT NULL PRIMARY KEY,
  [IsWeekend] INT  NOT NULL,
  [IsWeekday] INT  NOT NULL,
);

--------------------------------------------------------------------------------
-- Create Date list
-- Using Number sequence: https://datablog.roman-halliday.com/index.php/2018/06/01/dates-in-sql-server-create-sample-date-ranges/
--------------------------------------------------------------------------------
DECLARE @DateStart DATE = '01-JAN-18';
DECLARE @DateEnd   DATE = EOMONTH('01-MAR-18');
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
), date_sequence AS (
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
)
INSERT
  INTO date_t
      ([TheDate], [IsWeekend], [IsWeekday])
SELECT [TheDate],
       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 1 ELSE 0 END AS [IsWeekend],
       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 0 ELSE 1 END AS [IsWeekday]
  FROM date_sequence
;

The questions

This is a result of some questions people asked me and proved an interesting challenge to create a neat solution for. This is around managing monthly processes, detecting if the present day meets the requirement, but also for planning in the future:

1. Can we flag if a date is the last Friday of the month?
2. Is there a way of detecting when it’s the last weekday of the month?

The solution

For this I’ve calculated the working day number as in my previous post, but I’ve also put a count together to highlight a given day of the week by including it’s day of the month number.

In both cases I’m comparing the rows value with the highest value for the month, and outputting 1 if that is met. There are other ways to get this sort of result, but this is the most reusable that I’ve found. It’s easy to change the selection criteria and the windowing criteria:

--------------------------------------------------------------------------------
-- Find the last X of the month
--------------------------------------------------------------------------------
WITH date_data AS (
SELECT [TheDate],
       DateName(dw, [TheDate]) AS TheDate_day_name,
       [IsWeekend],
       [IsWeekday],
       CASE [IsWeekday]
           WHEN 1
           THEN SUM(CAST([IsWeekday] AS INT)) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate])
           ELSE NULL
        END AS [WorkingDayNumber],
       CASE DateName(dw, [TheDate])
           WHEN 'Friday' -- Change this to find the day we are interested in
           THEN SUM(1)                        OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate])
           ELSE 0
        END AS [DayToTrackNumber]
  FROM date_t
)
SELECT [TheDate],
       [TheDate_day_name],
       [IsWeekend],
       [IsWeekday],
       CASE [TheDate] WHEN DateFromParts(YEAR(GetDate()), MONTH(GetDate()), DAY(GetDate())) THEN 'Today' ELSE '' END AS [DayMarker],
     --Find last weekday of month
       [WorkingDayNumber],
       CASE [WorkingDayNumber]
           WHEN MAX([WorkingDayNumber]) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]))
           THEN 1
           ELSE 0
        END [IsLast_WorkingDay],
     --Find last X day of month
       [DayToTrackNumber],
       CASE [DayToTrackNumber]
           WHEN MAX([DayToTrackNumber]) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]))
           THEN 1
           ELSE 0
        END [IsLast_DayToTrack]
  FROM date_data
;

See it in action over at rextester: SQL Server – Find the last X of the month