CODEWITHSUNDEEP
sqlhiveaqua-data-studio
adann96d
adann96d

Reputation: 41

Counting days between two days excluding weekend (Saturday and Sunday) in hive sql

the problem is simple, I have two dates and count all days between them by using datediff() function.

E.g.: datediff('23/08/2023', '30/08/2023') returns 7, but it should return 5, because 26-27/08/2023 are weekend days.

Do You know any function or have a practical solution how to count it? The simpler, the better. Thank You in advance.

Upvotes: 0

Views: 286

Answers (1)

WLefever
WLefever

Reputation: 385

This seems to be a pretty good solution.

In your case that would result in:

SELECT 
DATEDIFF(day, '2023/08/23', '2023/08/30') + 1 -
DATEDIFF(week, '2023/08/23', DATEADD(day, 1, '2023/08/30')) -
DATEDIFF(week, '2023/08/23', '2023/08/30')
AS DateDiff;

When executing this resulted in 6 weekdays which seems to be correct: 23, 24, 25, 28, 29, 30.

Upvotes: 0

Related Questions