nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using DateDiff to calculate week days only

Green, Desmond

I'm using a table transformer to just calculate week days only. I had some very complicated SQL and this wasn't working reliably (with some weird rounding going on), and then I realised DATEDIFF offered a weekday option - but it doesn't seem to be discounting the weekends.

For example, I was using this:

SELECT DATEDIFF(day, "01/01/2024", NOW()) 
- 2 * (DATEDIFF(day, "01/01/2024", NOW()) / 7) 
- CASE WHEN DAYOFWEEK("01/01/2024") IN (0, 6) THEN 1 ELSE 0 END 
- CASE WHEN DAYOFWEEK(NOW()) IN (0, 6) THEN 1 ELSE 0 END 
AS 'Days since start of year, excluding weekends'

But as I tested some future dates by swapping NOW() with literal dates, such as "03/02/2024", the calculation was not reliable. The date format I am using in the table transformer is dd/mm/yyyy

If I try and use this, then I don't get back the number of weekdays, only the full number of days. For example, today 29/01/2024 this is returning 28.


SELECT DATEDIFF(weekday, "01/01/2024", NOW()) 
AS 'Days since start of year, excluding weekends'

How do I just get the number of weekdays, Monday to Friday?

Accepted answers

Green, Desmond

Ok, this seems to work. I've used some variables and hard-coded dates to try this out.

DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = "01/01/2024";
SET @EndDate ="05/02/2024";

SELECT
(DATEDIFF(day, @StartDate, @EndDate) + 1)
-(DATEDIFF(week, @StartDate, @EndDate) * 2)
-(CASE WHEN DAYOFWEEK(@StartDate) = 6 THEN 2 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@StartDate) = 0 THEN 1 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@EndDate) = 6 THEN 1 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@EndDate) = 0 THEN 2 ELSE 0 END) as 'Counting week days only'

All comments

Green, Desmond

Ok, this seems to work. I've used some variables and hard-coded dates to try this out.

DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = "01/01/2024";
SET @EndDate ="05/02/2024";

SELECT
(DATEDIFF(day, @StartDate, @EndDate) + 1)
-(DATEDIFF(week, @StartDate, @EndDate) * 2)
-(CASE WHEN DAYOFWEEK(@StartDate) = 6 THEN 2 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@StartDate) = 0 THEN 1 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@EndDate) = 6 THEN 1 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@EndDate) = 0 THEN 2 ELSE 0 END) as 'Counting week days only'

Scott Gillespie_ibixpx

This solution doesn't work. I am struggling with the same problem. It seems every solution I find works for most but not all scenarios. Try 01/02/2025 and 01/15/2025 as your dates. Result will be 12 days, when it should be 10.

Remember to check the workday settings in Table Transformer. It defaults to a 5 day workweek, but any of these solutions won't work unless you change the settings to indicate a "week" is 7 days, 24 hours.

Stiltsoft support

Hi @Scott Gillespie ,

You may an example from our documentation regarding the topic.

If it works not as expected for you or if your case is a more complicated one, please refer to our support portal.

Green, Desmond

I can see that the approach in the Confluence documentation mentioned does give the result of 10, when trying this out with start date 01/02/2025 and end date 01/15/2025.

SELECT *,
'Days' -
2 * (('Days' / 7)::integer) -
CASE
WHEN 'Days' % 7 = 0 THEN 0
WHEN 'Start date'::Date->getDay() = 0 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((T1.'End date' - T1.'Start date') / "1d") + 1 AS 'Days'
FROM T1)

In my use case, I just wanted to adapt this to calculate the start and end date dynamically, and not from data in the table. The end date is always today, and the start date is either 30 days previously or the first day of the year. This is to give 30 day rolling periods, or otherwise as many days have so far occurred, in the calendar year. I use these sort of calculations in metrics for the calendar year.

So, I tried to use variables to substitute the 'Start date' and 'End date' columns, as per the below. Unfortunately, this returns no rows. Maybe it is something reasonably straightforward syntactically or logically? Anyone, any ideas?

DECLARE @StartDate DATETIME = GREATEST(DATEADD(day,-30, GETDATE()),"01/01/2025");
DECLARE @EndDate DATETIME = GETDATE();

SELECT 'Days' -
2 * (('Days' / 7)::integer) -
CASE
WHEN 'Days' % 7 = 0 THEN 0
WHEN @StartDate::Date->getDay() = 0 THEN 1
WHEN @StartDate::Date->getDay() + 'Days' % 7 = 7 THEN 1
WHEN @StartDate::Date->getDay() + 'Days' % 7 > 7 THEN 2
ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((@EndDate - @StartDate) / "1d") + 1 AS 'Days'
FROM T1)

Katherine Buttars

If your SQL DATEDIFF queries are returning inconsistent weekday counts, skip the hassle. Use this easy days calculator to accurately find the number of weeks or weekdays between any two dates, no complex logic needed.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets