Using DateDiff to calculate week days only

Green_ Desmond
Contributor
January 29, 2024

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?

1 answer

1 accepted

2 votes
Answer accepted
Green_ Desmond
Contributor
January 29, 2024

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
Contributor
January 14, 2025

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 14, 2025

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
Contributor
January 15, 2025

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)

 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 15, 2025

Please create a support request - we'll try to look into the case more closely.

Meanwhile, not all JS methods are supported by the Table Transformer macro, it's based on the AlaSQL library and supports its syntax.

So you may try to simplify your case:

  • use the simple today variable to get the today date
  • then use the DATEADD/DATESUB functions to calculate the start date from the "today" variable

Not sure what you mean by "not from data in the table" but the macro works with tables. So, you may create a blank one row table inside the macro body and populate it with the today date and calculated start date using the SQL query. 

Then in a separate Table Transformer on top you'll be able to use the query from the documentation.

 

Green_ Desmond
Contributor
January 15, 2025

Ok, thanks. I use the Table Transformer, as it gives me the ability to access and leverage SQL type language in Confluence and completely dynamically calculate values. I can also use this in combination with actual table data, in different use cases.

For example, if my Table Transformer has a completely empty table, I can still do this.

I also think this logic is now working, having tried a few more things.

The working days (Monday to Friday) calculated between 1 January 2025 and 15 January 2025 is being returned as 11. @Scott Gillespie Maybe check this out, I also hard coded 2 January as start date and 15 January as end date and this returns 10, inclusive of the start and end dates. I haven't tested anything other than these two scenarios!

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

DECLARE @Days INT = DATEDIFF(day, @StartDate, @EndDate);

DECLARE @WeekDays INT;

SET @WeekDays = (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) + 1;

SELECT @WeekDays AS 'Counted Value'
Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events