Duration calculation between two dates exclude weekend in confluence?

[Godwin jino]

Hi all,

i have created a table macro to calculate number of days between two dates (start date to end date). below i have mentioned the query . could you help me to modify to exclude weekends? 

select *,(CAST(ROUND(ceiling(DATEDIFF(DAY,'Start Date','END Date')))as int))+1+ " days" as 'Duration' from T1




Thanks 

[Shawn Doyle - ReleaseTEAM · Accepted solution]

You could take the number of days, divide by 7 to get weeks, then multiply by 2 to get the weekends, and then subtract that value from the number of days.  Should get you fairly close.

Some SQL examples can be found here: https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server

[Godwin jino]


hi Doyle ,
i have updated my query, what are the filed to be modified to complete this table in settings?


 
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)

[Godwin jino]

hi @Shawn Doyle - ReleaseTEAM  ,

i have tired above code  working now.