For reporting or for process management, it’s handy to be able to quickly identify weekdays and weekends. Here I’ll look at a few methods for calculating weekdays and weekends.<\/p>\n
Some of these come easily from SQL Server, others require a bit more manipulation. Most of this builds on my previous articles on dates:<\/p>\n
Note: I’m going to look into\u00a0public (bank) holidays and geographic variations in a later post.<\/p>\n
From my previous article, the below SQL will create a date range collection:<\/p>\n
--------------------------------------------------------------------------------\r\n-- Create Date Table\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE date_t\r\n(\r\n [TheDate] DATE NOT NULL PRIMARY KEY\r\n);\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create Date list\r\n-- Using Number sequence: https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/01\/dates-in-sql-server-create-sample-date-ranges\/\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateStart DATE = '01-JAN-17'; -- At time of writing this is the past\r\nDECLARE @DateEnd DATE = '01-JAN-19'; -- At time of writing this is the future\r\n \r\nPRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);\r\nPRINT 'Start End: ' + CONVERT(VARCHAR(10), @DateEnd, 120);\r\n\r\nWITH number_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n), number_sequences AS (\r\nSELECT t1.a AS t1a,\r\n t2.a AS t2a,\r\n t3.a AS t3a,\r\n (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation\r\n FROM number_tbl t1\r\n CROSS JOIN number_tbl t2\r\n CROSS JOIN number_tbl t3\r\n)\r\nINSERT\r\n INTO date_t\r\n ([TheDate])\r\nSELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]\r\n FROM number_sequences\r\n WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd\r\n ORDER BY concatenate_calculation\r\n;<\/pre>\nIdentifying the weekday and weekend<\/h1>\n
Weekends can be calculated by day name, or number. Both have their advantages and disadvantages:<\/p>\n
Below I’m showing how to use both, I’ve not put in the regional settings as people will normally work in one language or another. I have included the DateFirst configuration. This makes a nice template which can assign the setting for the session.<\/p>\n
SET DATEFIRST 1;\r\n\r\nSELECT @@DateFirst AS [DateFirst Setting];\r\n\r\nSELECT [TheDate],\r\n DateName(dw, [TheDate]) AS TheDate_day_name,\r\n DatePart(dw, [TheDate]) AS TheDate_day_of_week,\r\n CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 1 ELSE 0 END AS [IsWeekend], -- Using numeric day of week\r\n CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 0 ELSE 1 END AS [IsWeekday], -- Using numeric day of week\r\n CASE WHEN DateName(dw, [TheDate]) IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END AS [IsWeekend], -- Using text of day name\r\n CASE WHEN DateName(dw, [TheDate]) IN ('Saturday', 'Sunday') THEN 0 ELSE 1 END AS [IsWeekday] -- Using text of day name\r\n FROM date_t\r\n;<\/pre>\nSee this in action on\u00a0rextester:\u00a0SQL Server – Weekdays and Weekends<\/a><\/p>\nAlso see:<\/p>\n
\n- DatePart<\/a><\/li>\n
- DateName<\/a><\/li>\n<\/ul>\n
Calculate working day number<\/h1>\n
To work out which weekday it is, in the month, I use windowing functions to add all the IsWeekday values up to and including the current date. In this instance I have partitioned by year and month so that I get rolling numbers for each month, but there is no reason this technique can’t be applied to any other period.<\/p>\n
--------------------------------------------------------------------------------\r\n-- Calculate working day number (of the month)\r\n--------------------------------------------------------------------------------\r\nSELECT [TheDate],\r\n DateName(dw, [TheDate]) AS TheDate_day_name,\r\n [IsWeekend],\r\n [IsWeekday],\r\n CASE [IsWeekday] WHEN 0 THEN NULL\r\n ELSE SUM(CAST([IsWeekday] AS INT)) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate])\r\n END AS [WorkingDayNumber],\r\n CASE [TheDate] WHEN DateFromParts(YEAR(GetDate()), MONTH(GetDate()), DAY(GetDate())) THEN 'Today' ELSE '' END AS [DayMarker]\r\n FROM date_t\r\n;<\/pre>\n