Calculate the difference between two dates in SQL, excluding holidays

Question

I would like to build an SQL query that calculates the difference between two dates, without taking into account the days off in the results.

Is there a way to format dates to get this result? For example, for an Oracle database:

select sysdate - creation_dttm from the_table 

Answer 1

I found another way to calculate the difference using only SQL:

 select sysdate - creation_dttm - 2 * (to_char(sysdate, 'WW') - to_char(creation_dttm, 'WW')) from the_table 

Answer 2

You should try using the function:

 CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE) RETURNS INT RETURN ABS(DATEDIFF(date2, date1)) + 1 - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY), ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2 - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1) - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7); 

Test:

 SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1, TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2; 

Result:

 | WEEKDAYS1 | WEEKDAYS2 | ------------------------- | 13 | 13 | 

Answer 3

I found several answers on this topic so as not to do what they require. After some experimentation, testing, and tuning, I have to contribute it.

 declare @firstdate Date declare @seconddate Date set @firstDate = convert(date, '2016-03-07') set @seconddate = convert(date, '2016-04-04') select (datediff(dd, @firstDate, @secondDate)) - (( DateDiff(wk, @firstDate, @secondDate) * 2) - case when datepart(dw, @FirstDate) = 7 then 1 else 0 end - case when datepart(dw, @secondDate) = 7 then -1 else 0 end) 

In the bundle of the tested harness - you can simply set up two dates and conduct your own tests. This assumes the difference between two adjacent dates on weekdays: 1. If your country uses different days to indicate holidays, you will need to set the date base accordingly, so that your “Saturday” is 7 and your “Sunday” is 1 .

Answer 4

From a previous post :

 DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SET @StartDate = '2008/10/01' SET @EndDate = '2008/10/31' SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) 

Answer 5

Here is an example:
There are four variables, the first two are self-evident, just enter the date in YYYYMMDD format, the third is to set the number of normal working days for a given week, so if the site is working 6 days a week, set it to 6, five days a week - 5, etc. .d. Finally, DATE_SEQ_NORML_FACTOR must be 1 when working with Oracle. This means that in July, 2 on Tuesday, etc., Julian date alignment, etc. will be used. When using MOD 7., another database will likely have different values ​​between 0 and 6, so check it before using against other databases.

Here are the limitations: 1. This formula assumes that the first day of the week is MONDAY. 2. This formula assumes that all days for one week are CONTINUOUS. 3. This formula will work ONLY when the two dates involved in the calculation relate to a weekly or working day, for example. The “Start Date” on SATURDAY when the place is only MON-FRI will not work.

 SELECT &&START_DATE_YYYYMMDD "Start Date", --in YYYYMMDD format &&END_DATE_YYYYMMDD "End Date", --in YYYYMMDD format &&WK_WORK_DAY_CNT "Week Work Day Count", --Number of work day per week &&DATE_SEQ_NORML_FACTOR "Normalization Factor", --set to 1 when run in Oracle CASE WHEN FLOOR( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR / 7 ) = FLOOR( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR / 7 ) THEN( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) - TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + 1 ) ELSE( ( &&WK_WORK_DAY_CNT - MOD( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) + 1 ) + MOD( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) + ( ( ( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) - MOD( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) ) - ( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + ( 7 - ( MOD( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) ) ) ) ) / 7 * &&WK_WORK_DAY_CNT ) ) END "Week Day Count" FROM DUAL 

Answer 6

We have included the following logic in several reports with great success. Sorry, I no longer remember the source of this script to give them credit.

 DECLARE @range INT; SET @range = DATEDIFF(DAY, @FirstDate, @SecondDate); SET @NumWorkDays = ( SELECT @range / 7 * 5 + @range % 7 - ( SELECT COUNT(*) FROM ( SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 ) weekdays WHERE d <= @range % 7 AND DATENAME(WEEKDAY, @SecondDate - d) IN ('Saturday', 'Sunday')) ); 

Answer 7

I updated the @JOpuckman function to take into account that there are not always weekend Saturdays and Sundays in different regions. Here's the code in case anyone else needs to apply this globally;

 DECLARE @FirstDate DateTime DECLARE @SecondDate DateTime SET @FirstDate = '08-20-2012' SET @SecondDate = '08-24-2012' DECLARE @range INT; DECLARE @WeekendDayNameStart VARCHAR(50) DECLARE @WeekendDayNameEnd VARCHAR(50) SET @WeekendDayNameStart = 'FRIDAY' SET @WeekendDayNameEnd = ( SELECT CASE @WeekendDayNameStart WHEN 'SUNDAY' THEN 'MONDAY' WHEN 'MONDAY' THEN 'TUESDAY' WHEN 'TUESDAY' THEN 'WEDNESDAY' WHEN 'WEDNESDAY' THEN 'THURSDAY' WHEN 'THURSDAY' THEN 'FRIDAY' WHEN 'FRIDAY' THEN 'SATURDAY' WHEN 'SATURDAY' THEN 'SUNDAY' END ) DECLARE @NumWorkDays INT SET @range = DATEDIFF(DAY, @FirstDate, @SecondDate); SET @NumWorkDays = ( SELECT @range / 7 * 5 + @range % 7 - ( SELECT COUNT(*) FROM ( SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 ) weekdays WHERE d <= @range % 7 AND DATENAME(WEEKDAY, @SecondDate - d) IN (@WeekendDayNameStart, @WeekendDayNameEnd)) ); -- Calculate whether the current date is a working day DECLARE @CurDateExtra INT SET @CurDateExtra = ( CASE DATENAME(WEEKDAY, @SecondDate) WHEN @WeekendDayNameStart THEN 0 WHEN @WeekendDayNameEnd THEN 0 ELSE 1 END ) SET @NumWorkDays = @NumWorkDays + @CurDateExtra SELECT @NumWorkDays 

Answer 8

I have a problem with @lightmania answer.

Use of values:

 select (TO_DATE('06/02/2014', 'DD/MM/YYYY') - TO_DATE('04/02/2014', 'DD/MM/YYYY') - 2 * (to_char(TO_DATE('06/02/2014', 'DD/MM/YYYY'), 'WW') - to_char(TO_DATE('04/02/2014', 'DD/MM/YYYY'), 'WW'))) from dual; 

Returned 0 instead of 2, which he should have returned.

Answer 9

• Calculates the difference in days between two dates
• Calculates the difference in week numbers and year numbers, subtracts the number of weeks, and then multiplies the result by 2 to calculate the number of days off between two dates. If the year numbers differ from the difference in 52 * year + week number.

((sysdate - ced.created_dt) + ((((to_char(ced.created_dt,'IW') - ((to_char(sysdate,'YY') - to_char(ced.created_dt,'YY'))* 52)) - to_char(to_char(sysdate,'IW')))) * 2)) duration_in_weekdays

Answer 10

You can try the following:

 SELECT Id, DATEDIFF(d, datefrom, dateto) AS TotDays, DATEDIFF(wk, datefrom, dateto) AS Wkds, DATEDIFF(d, datefrom, dateto) - DATEDIFF(wk, datefrom, dateto) AS Days FROM YOURTABLE