How do you calculate the number of working hours (not just working days) between two date/time values in SQL, excluding weekends, and ideally also respecting any configured non-working/holiday exceptions, rather than hand-rolling day-of-week logic?

All FAQ questions / Reporting and analytics

Answer

Rather than manually excluding weekends with CASE-based day-of-week logic in raw SQL, use the framework's own Work Time Calendar API against the relevant Site's Distribution Calendar — obtain the calendar ID via Site_API.Get_Dist_Calendar_Id('<SITE>'), then pass it along with your two dates into the appropriate Work Time Calendar API working-time calculation method for the unit you need (working days, or the equivalent minutes/hours-based method depending on your specific requirement and IFS version). This correctly accounts for whatever non-working days and schedule exceptions are actually configured on that calendar, rather than only excluding a fixed Saturday/Sunday pattern.

Documentation-based answer. Confirm the product and release before applying it. This answer has not been verified in an IFS environment.

← Browse Reporting and analytics questions