Exclude weekends and public holiday for SQL datediff

how to exclude weekends in sql query
sql datediff hours exclude weekends
sql dateadd exclude weekends
sql datediff weekdays
sql business days holidays
federal holidays sql
snowflake datediff excluding weekends
date diff

this code works but seems like the results are off by 1 day

Objective: Find the datediff for the two dates given.

However, we will have to exclude

  • Weekends
  • Public Holiday


The difference between 2017-11-04 00:00:00 and 2017-11-22 10:21:00

  • Expected Results: 12.42 (Excluded 3 Saturdays & Sundays)

  • Current Results: 13.42

The code is not written by me, so i'm trying to understand it. I commented some parts that i am unclear as well.

/**This part is to find the difference in time for the two dates**/
DECLARE @temp AS DECIMAL(10,2) = CAST(DATEDIFF(hour, CONVERT(time, @StartDate), CONVERT(time, @ENDDate)) as Decimal(10,2))/24.00

Declare @numdays int=0

/**This part is to find the difference in date for the two dates**/
/**Issues likely to come from here**/
IF DATEDIFF(day,@StartDate,@ENDDate)>0 
    WHILE DATEDIFF(day,@StartDate,@ENDDate)>0
        SET  @StartDate=DATEADD(d,1,@StartDate) 
        WHILE exists (SELECT Holiday_Date FROM Holiday where Holiday_Date=CONVERT(date, @StartDate))  or DATENAME(DW,@StartDate)='saturday' or DATENAME(DW,@StartDate)='sunday'
                SET  @StartDate=DATEADD(d,1,@StartDate)
                /**Exclude weekends and PH so we do not add numDays here **/
        SET @numDays=@numDays+1

/**Omitted some irrelevant codes here**/

/**Add both differences together to get final result**/
DECLARE @result AS DECIMAL(10,2) = @temp + @numDays

RETURN @result

Was thinking to just correct the logic instead of rewriting the entire code. Thanks!

Like has been mentioned, the best way is to create a Calendar Table and then use the Working Days column, or whatever it is you called it, to calculate the difference.

This is pseudo-SQL, in the absence of a working Calendar Table, but should get you there:

SELECT YT.{YourColumn},
FROM YourTable YT
     CROSS APPLY (SELECT COUNT(CT.DateKeyColumn) -1 AS WorkingDays --Minus 1, as we don't want to include the first day
                  FROM CalendarTable CT
                  WHERE CT.[DateColumn] >= YT.StartingDateColumn
                    AND CT.[DateColumn] <= YT.EndingDateColumn
                    AND CT.WorkingDay = 1) WD

Solved: Datediff excluding weekends and public holidays, Solved: Hi how to count the days from a date picker from and date picker to excluding weekends and public holiday? I assume that I need to create a. I am looking for solution how to select number of days between two dates without weekends and public holidays. everything works fine untill I uncomment section: What I am trying to achieve is to get number of working days in date range. Problem is with last step, where I am trying to substract all public holiday days from this range.

The code you mentioned does essentially the following in pseudocode:

while i between @StartDate and @ENDDate
    increment i by 1
    increment i until it is not a holiday
    add one day to result
return result

My opinion is that this code is ugly and inefficient.

  1. It uses @StartDate as both the "input parameter", ie the true starting date, AND the loop variable. That is confusing.
  2. The use of loops and cursors are efficient in very specific cases, and this certainly is NOT one of them.

90% of the hassle in your kind of question is the creation of the calendar table; but you already have one. Do not use this function; change the code to what Larnu suggests. Ask us further clarification if you encounter difficulty doing so.

How to count the business days between two dates, excluding , This means excluding weekends and holidays from that count. The “DateDiff” function allows us to calculate the difference between two dates, “What is the best way to get difference between two dates in SQL Server” blog. This calendar includes ten holidays that are observed by the Federal Reserve. You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection. i gave you example here.

Tested in MySQL 5.1: MySQL's "weekday" function returns 0 for Mon, 5 for SAT and 6 for SUN, thus you see below SQL has some magic no. with 5 and 6.


select  floor((datediff (ed, st)+1) / 7)*2 /*complete week's weekends*/
        + case when floor((datediff (ed, st) +1) % 7) between 0 and 6 /*additional weekends besides complete weeks*/
                then case when weekday(ed) >= weekday(st) then least(floor((datediff (ed, st) +1) % 7), greatest(least(6, weekday(ed)) - greatest(5, weekday(st)) + 1,0))
                                else least(floor((datediff (ed, st) +1) % 7), greatest(least(6, weekday(ed)+7) - greatest(5, weekday(st)) + 1,0)) end
            else 0
        end as num_of_sat_and_sun
from (select '2019-01-07' as st, '2019-01-12' as ed) x

SQL Script to Exclude Weekends and Holidays, This is really a T-SQL question and would be better off on StackOverflow. That said, you can use the following to determine if the date falls on a weekend: DATEDIFF - Exclude weekends and holidays. Subscribe to RSS Feed. Email to a Friend. Report Inappropriate Content. ‎05-09-2019 02:37 AM. In this particular example, I do not have a relationship with Calendar Table, this needs to be filtered within the same table. Therefore, I would like to get the DATEDIFF between two dates, and within the

How to exclude weekends and holidays in a query, You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any.Here Sunday is set as a firstday of week, For  "d" gives me the total days (counting the weekends and holidays) example - 12/21/06 to 01/03/07 = 13 days "w" gives me the number of full 7 day weeks - example 12/21/06 to 01/03/07 = 1 Any help will be appreciated!

Date Calculation Excluding Weekend & Holiday – SQLServerCentral, Date Calculation Excluding Weekend & Holiday – Learn more on the SQLServerCentral forums. Datediff(day,creation_date,getdate())as Outstanding​. I want to find However, I don't think there is a similar function in T-SQL. The statement DATEDIFF(wk,@fromdate,@todate) gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count. The next two statements excludes the day if it’s a Saturday or Sunday.

SQL Server – Calculating Business Days Between Two Dates (With , SQL Server – Calculating Business Days Between Two Dates (With Holiday Exclusion) DATEDIFF( day , a.start_date, a.end_date) AS reponse_time_in_days It is not a good idea to hard-code the list of holidays in your query. General (2), Oracle (74), ADRCI (1), Alert Log (3), Analytic Functions (5)  Re: exclude holidays when counting days using datediff Tom W Oct 30, 2014 9:18 AM ( in response to Alexis Jasinski ) If you're pulling in from SQL, then I'd do this in the database.

  • Best way to do is using calendar table where you can also define local public holidays has the option to exclude if required
  • Which public holidays? Every country is different.
  • The values for the public holiday table is assumed correct in this case. The example given does not include any public holiday.
  • @JAYY assumed by whom? The server has no idea what the developer's assumptions are. Use a calendare table instead
  • Hi, thanks for ur answer. But i'm still confused why the code added an extra day. Do you know why?
  • Notice how Larnu's code has a count ()-1? This is because the difference between two numbers is the count of all numbers between first and last minus one. Eg: the difference between 4 and 6 is 2: 3 numbers total (4,5,6) but you have to substract 1. This is missing in the function; instead of calculating the difference, it calculates non-holiday days, which is always one more.