How can I calculate business hours between two dates? It also takes into account any holidays which must be defined in a table. Create a table with all the dates you want to consider in it.

sql - Count work days between two dates - Stack Overflow

For each day, set a number of working hours, like so:. This will take a small amount of setting up -- you can pre-populate it for weeks versus weekends automatically, then adjust for public holidays, etc, as necessary. It also makes the rules more easily "editable", as you don't need to change any actual code to change the definitions of a working day, add public holidays, etc.

The question says that public holidays should not be considered, so this answer does just that - calculates business hours taking weekends into account, but ignoring possible public holidays.

With this assumption the code doesn't care about the time when the business day starts or ends, it cares only about the total number of business hours per day. In your example, there are 8 business hours between It doesn't have to be a whole number. The formula below calculates it with one minute precision, but it is trivial to make it one second or any other precision.

If you need to take public holidays into account you'd need to have a separate table which would list dates for public holidays, which may differ from year to year and from state to state or country to country.

The main formula may stay the same, but you'd need to subtract from its result hours for public holidays that fall within the given range of dates. It works, because in SQL Server DATEDIFF returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

Each day has 8 business hours.

I have actually done this before, taking into account all the variables weekends, holidays, etc for business hours is very difficult, I think this task is best done outside SQL. Another way of thinkingthe below function work correctly if your first day of week is Monday otherwise you should change related lines including 6,7 to your local weekend's days. The version with the while loop is too slow for our workflow. The loop version above takes ten minutes on our SQL server if we run the calculation with several years between StartDate and FinishDate for one million rows.

My version completes the same task in 1 minute. Here's an alternative solution, without the use of a function.

No. of Saturdays within a date range / between two dates

Note that this relies on the existence of a numbers tablepopulated with at least the maximum number of days the tasks you're tracking may take. This doesn't take public holidays into account.

Calculating the number of working days between two dates - The SAS Dummy

If you don't work weekends, setting the opening and closing times to midnight in the OpeningHours table variable should do the job. Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site the association bonus does not count. Would you like to answer one of these unanswered questions instead?

Stack Overflow Questions Developer Jobs Documentation beta Tags Users. Sign up or log in to customize your list. Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us.

Log In Sign Up. Join the Stack Overflow Community. Stack Overflow is a community of 7. Join them; it only takes a minute: Calculate business hours between two dates Ask Question. Baran 3 11 Yes, I dont take national holidays and breaks. Baran's answer fixed and modified for SQL SQL and above: Baran Kaynak modified by Kodak -- Create date: Kodak 1, 2 12 I have a question here, Temp returns the difference in minutes only?

Just so you know there calculate weekends between two dates sql a bug in this fine solution. The second if block needs a check to 247 sure win binary option if the number is negative and if so to exclude it.

Otherwise the time after end of work will subtract time. I added the following lines to fix the bug that JStead mentions above: These can be added right before the declaration of CurrentDate.

Gugg Oct 13 '16 at The function returns the interval in minutes - you can divide by 60 to get hours as required. This has been tested on SQL Server Hope it helps someone.

Thanks but I have hours in start and finish dates. How can we add this future in this calculation? An alternative solution from Pavanred's, coming at things from a more data-based angle: For who controls the binary option brokers day, set a number of working hours, like so: But, what you lose in the setting up, you gain in ease of querying: Matt Gibson 29k 6 69 Baran Kaynak -- Create date: Peer didn't like me edits so I will post a new answer: Ant Swift 5, 6 27 money maker v2.4 free download Ben Mejores brokers forex online 11 2.

DECLARE T TABLE StartDT datetime2 0EndDT datetime2 0 calculate weekends between two dates sql INSERT INTO T VALUES ' BlackTigerX 4, 4 27 What do you think about this solution?

Without using loop "While". WorkingDaysBetweenDates StartDate, EndDate - dbo. Below is the function without a loop tested on for your reference: WorkTime EventStartDateTime DATETIME, EventEndDateTime DATETIME, BusinessStartDateTime DATETIME, BusinessEndDateTime DATETIME, IncludeWeekend BIT RETURNS BIGINT AS BEGIN -- Purpose: Function that calculates seconds of business hours between an event.

Event StartDateTime ESDTEvent EndDateTime EETD -- Business StartDateTime BSDTBusiness EndDateTime BEDT -- Event StartHour ESHEvent EndHour EEH -- Business StartHour BSHBusiness EndHour BEH -- For 24x7 business hours pass these parameters to the function: SET DATEFIRST 7; SELECT dbo.

Mathias Florin 1 The solution by mathias florin is clean an easy but there is a bug with condition If using business hours 8 to 5 and the event start time is after 5pm and finished the next day at 9am. Another fail with condition 16 is if started after 5pm and finished before 8am the next day.

If the event starts at This is correct as the business had the chance to work one hour sec in the morning Regarding your second fail, if the event starts after business hours and finishes before business hours in the morning, condition 16 returns The result is unioned with 0 cond.

MAX ,0 returns 0 which is correct - no time spent on event. I've tested this against rows of 'real world' data and found it to be performant.

DECLARE OpeningHours TABLE [DayOfWeek] INTEGER, OpeningTime TIME 0ClosingTime TIME 0 ; INSERT OpeningHours [DayOfWeek], OpeningTime, ClosingTime VALUES 1, ' CompletedDateTime AS DATE THEN CASE WHEN CAST Tasks.

OpeningTime THEN -- Task created before opening time DATEDIFF MINUTE, OpeningHours. CompletedDateTime AS TIME ELSE DATEDIFF MINUTE, Tasks. CompletedDateTime END ELSE CASE WHEN Tasks. CreatedDateTime AS DATE THEN -- This is the day the task was created CASE WHEN CAST Tasks. ClosingTime THEN 0 -- after working hours ELSE -- during or before working hours CASE WHEN CAST Tasks.

OpeningTime THEN -- before opening time; take the whole day into account DATEDIFF MINUTE, OpeningHours. ClosingTime ELSE -- during opening hours; take part of the day into account DATEDIFF MINUTE, CAST Tasks. CreatedDateTime AS TIMEOpeningHours. ClosingTime END END ELSE -- This is the day the task was completed CASE WHEN Tasks. OpeningTime THEN 0 -- before working hours unlikely to occur ELSE -- during or after working hours CASE WHEN CAST Tasks. ClosingTime THEN -- after closing time also unlikely ; take the whole day into account DATEDIFF MINUTE, OpeningHours.

ClosingTime ELSE -- during opening hours; take part of the day into account DATEDIFF MINUTE, OpeningHours. CompletedDateTime AS TIME 0 END END ELSE DATEDIFF MINUTE, OpeningHours.

ClosingTime END END ENDTasks. CompletedDateTime FROM SELECT Tasks. Number Tasks INNER JOIN OpeningHours OpeningHours ON DATEPART WEEKDAY, Tasks. CompletedDateTime ORDER BY Tasks.

Stack Overflow works best with JavaScript enabled. I assume you don't need to take national holidays etc into account? I have actually done this before, taking into account all the variables weekends, holidays, etc for business hours is very difficult, I think this task is best done outside SQL share improve this answer. CompletedDateTime; share improve this answer. MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3.

Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.