SQLTeam.com | Weblogs | Forums

Datediff in business hours

bonjour,
j'aimerais calculer la différence entre deux datetime en tenant compte des horaires de travail et les jours fériers,
j'ai trouvez un script qui me convient mais malheureusement ce script tien compte les horaire de travail de lundi à samedi de 07:00 à 15:00 or je veut le modifier de tel sorte que l'horaire de travail pour samedi soit de 07:00 à13:00, aussi ce script exclue les dimanche, mais je veut aussi qu'il exclue aussi les jours fériers.
quelqu'un peut m'aidé svp?

USE [TYPE]
GO
/****** Object: UserDefinedFunction [dbo].[WorkTime] Script Date: 01/04/2021 16:54:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0

DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)

DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)

DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)

DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)

DECLARE @WorkStart TIME
SET @WorkStart = '07:00'

DECLARE @WorkFinish TIME
SET @WorkFinish = '15:00'

DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

IF (@StartTime<@WorkStart)
BEGIN
    SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
    SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
    SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
    SET @StartTime = @WorkFinish
END

DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay

WHILE(@CurrentDate<=@LastDate)
BEGIN       
    IF (DATEPART(dw, @CurrentDate)!=7)
    BEGIN
        IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
        BEGIN
            SET @Temp = @Temp + @DailyWorkTime
        END
        --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
        ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
        BEGIN
            SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
        END

        ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
        BEGIN
            SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
        END
        --IF it starts and finishes in the same date
        ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
        BEGIN
            SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
        END
    END
    SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END

-- Return the result of the function
IF @Temp<0
BEGIN
    SET @Temp=0
END
	
RETURN @Temp

END

Ce script est ecrit par quelqun autre. Mieux que tu nous fourni

  1. Ssmple data
  2. Le resulta desire

bonjour,
j'ai une table hours pour stocké les horaires par jours de la semaine :

work_day open_time close_time
Monday 07:00:00 15:00:00
Tuesday 07:00:00 15:00:00
Wednesday 07:00:00 15:00:00
Thursday 07:00:00 15:00:00
Friday 07:00:00 15:00:00
Saturday 07:00:00 13:00:00

et une deuxième table pour stocker les jours fériers:

holiday month annee

|03-05-2021|avril|2021

  1. le résultat souhaité est comme suit :datediff(minute,'30-04-2021 07:00','04-05-2021 07:10')=8h+6h+10min (8h le30-04+ 6h le 01-05+ 0h le 02-05(dimanche)+0h le 03-05(férié)+10min le 04-05=>850min
    merci pour votre aide

fournit les sample tables comme suit

create table #sample1(work_day varchar(50), open_time time, close_time time)

insert into #sample1
select' Monday', '07:00:00', '15:00:00'

aides nous a t''aider

le suivant n'est pas le resulta desire mais SQL script

datediff(minute,'30-04-2021 07:00','04-05-2021 07:10')=8h+6h+10min (8h le30-04+ 6h le 01-05+ 0h le 02-05(dimanche)+0h le 03-05(férié)+10min le 04-05=>850min

donne nous le resulta de la meme facons suivants

create table #resulta(work_day varchar(50), open_time time, close_time time)

insert into #resulta
select' Monday', '07:00:00', '15:00:00'

bonjour,
create table #hours
(
work_day varchar(10),
open_time varchar(8),
close_time varchar(8)
)
insert into #hours values ('Monday', '07:30:00', '15:00:00')
insert into #hours values ('Tuesday', '07:00:00', '15:00:00')
insert into #hours values ('Wednesday', '07:00:00', '15:00:00')
insert into #hours values ('Thursday', '07:00:00', '15:00:00')
insert into #hours values ('Friday', '07:00:00', '15:00:00')
insert into #hours values ('Saturday', '07:00:00', '13:00:00')

create table #holidays
(
[holiday] varchar NULL,
[month] varchar NULL,
[annee] varchar NULL,
)
insert into #holidays values ('02-02-2021','avril',2021)

create table #resultat
(
start_date varchar(50),
end_date varchar(50),
diff_min int
)
insert into #resultat values ('30-04-2021 07:00', '04-05-2021 07:10', '850')

datediff (850) soit la différence entre start_date et end_date en horaire de travail
merci pour votre réactivité

1 Like

as tu tester ce script sur ton propre SQL server

I get 1800 minutes = 30 hours:

DECLARE @end_date datetime
DECLARE @day_count tinyint
DECLARE @start_date datetime

SET @start_date = '20210330 07:00'
SET @day_count = 6
SET @end_date = DATEADD(MINUTE, 10, DATEADD(DAY, @day_count, @start_date))

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS numbers(number)
)
SELECT 
    --curr_date, h.open_time, h.close_time, ca1.open_time, ca1.close_time, t.number,
    SUM(
	    DATEDIFF(MINUTE, CASE 
	    WHEN t.number = 0 THEN CASE WHEN ca1.open_time > h.close_time THEN '00:00:00' 
		    WHEN h.open_time > ca1.open_time THEN h.open_time ELSE ca1.open_time END
        WHEN t.number = @day_count THEN CASE WHEN h.open_time > ca1.close_time THEN '00:00:00'
		    WHEN ca1.close_time > h.open_time THEN h.open_time ELSE ca1.close_time END
		ELSE h.open_time END, CASE
        WHEN t.number = @day_count THEN CASE WHEN ca1.close_time < h.open_time THEN '00:00:00'
		    WHEN ca1.close_time < h.close_time THEN ca1.close_time ELSE h.close_time END
		ELSE h.close_time END)
	) AS total_work_mins
FROM cte_tally10 t
CROSS APPLY (
    SELECT DATEADD(DAY, t.number, @start_date) AS curr_date,
	    CAST(@start_date AS time) AS open_time, CAST(@end_date AS time) AS close_time
) AS ca1
INNER JOIN #hours h ON h.work_day = DATENAME(WEEKDAY, curr_date)
WHERE 
    t.number BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date) AND
	h.work_day IS NOT NULL AND
	NOT EXISTS(SELECT 1 FROM #holidays h2 WHERE h2.holiday = CAST(curr_date AS date))

thank you ScottPletcher for réactivity
but i want calculate the différence between two date in minute in business hours and excluding holidays
so datediff(minute,@start_date,@end_date ) must return only minute worked (7,5h+6h+0+0+10min) =820min based on #hours and #holidays

In detail, I get this. You could see this by uncommenting the first line of the SELECT and commenting out the SUM. That's why I included that line, so you could see the details to find any issues.

curr_date open_time close_time open_time close_time number minutes
2021-04-05 07:00:00.000 07:30:00 15:00:00 07:00:00.0000000 07:10:00.0000000 6 0
2021-03-30 07:00:00.000 07:00:00 15:00:00 07:00:00.0000000 07:10:00.0000000 0 480
2021-03-31 07:00:00.000 07:00:00 15:00:00 07:00:00.0000000 07:10:00.0000000 1 480
2021-04-01 07:00:00.000 07:00:00 15:00:00 07:00:00.0000000 07:10:00.0000000 2 480
2021-04-03 07:00:00.000 07:00:00 13:00:00 07:00:00.0000000 07:10:00.0000000 4 360

thank you for reply but i don't understand ,
between 2021-03-30 and 2021-04-05 exist 2021-04-02 but i dont see this row.
and when i execute this script it return NULL

I thought April 2 was intended to be a holiday, so I changed the date in the holiday table to 20210402. That's why it doesn't show up.

If it's not a holiday in your table, it should show up.

ok but it return NULL when i run it in my sql

I have no idea what code you're actually running. I have no idea what data you actually have.

I've provided what I can, given that you don't seem to want to make any more effort yourself. Good luck with this.

finally i have resolved the problem but for somme range it return NULL :slight_smile:
wel i have created a table calendar to stock all date of the year:

IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Calendar]
END

CREATE TABLE [Calendar]
(
[CalendarDate] DATETIME,
[jour] varchar(50),
[jour_n] varchar(50),
[Moies] varchar(50),
[anne] varchar(50),
[semaine_n] varchar(50)
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '01-01-2021'
SET @EndDate = DATEADD(d, 365, @StartDate)

While @StartDate < @EndDate
Begin
INSERT INTO dbo.Calendar
(
CalendarDate,
jour,
jour_n,
Moies,
anne,
semaine_n

)
Values 
(
    
    @StartDate  ,       -- DateValue
                          DATENAME(DAY,@StartDate), --jours value
                          DATENAME(WEEKDAY,@StartDate), --jours_name value
     DATENAME(MONTH,@StartDate), --month value
                          DATENAME(year,@StartDate),--year value
    DATEPART(week,@StartDate )--week value
    
)

Set @StartDate += 1

End
table hours
CREATE TABLE [dbo].[hours](
[work_day] varchar NULL,
[open_time] varchar NULL,
[close_time] varchar NULL
) ON [PRIMARY]

INSERT INTO [dbo].[hours] values ('Monday', '07:00:00', '15:00:00')
INSERT INTO [dbo].[hours] values ('Tuesday', '07:00:00', '15:00:00')
INSERT INTO [dbo].[hours] values ('Wednesday', '07:00:00', '15:00:00')
INSERT INTO [dbo].[hours] values ('Thursday', '07:00:00', '15:00:00')
INSERT INTO [dbo].[hours] values ('Friday', '07:00:00', '15:00:00')
INSERT INTO [dbo].[hours] values ('Saturday', '07:00:00', '13:00:00')
one other table for holidays
CREATE TABLE [dbo].[holidays](
[holiday] varchar NULL,
[month] varchar NULL,
[annee] varchar NULL,
[jour] varchar NULL
) ON [PRIMARY]
insert into [holidays] values ('02-02-2021','avril','2021','Mardi')
insert into [holidays] values ('05-04-2021','avril','2021','Lundi')
insert into [holidays] values ('02-02-2021','avril','2021','Samedi')
then i have the functionlike bellow

ALTER FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0

DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)

DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)

DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)

DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)

DECLARE @WorkStart TIME
SET @WorkStart = (select open_time  from [TYPE].[dbo].[hours]where work_day='Monday')

DECLARE @WorkFinish TIME
SET @WorkFinish = (  select [close_time]  from [TYPE].[dbo].[hours]where work_day='Monday')

DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

IF (@StartTime<@WorkStart)
BEGIN
    SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
    SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
    SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
    SET @StartTime = @WorkFinish
END

DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay

WHILE(@CurrentDate<=@LastDate)
BEGIN       
    IF (DATEPART(dw, @CurrentDate)!=7)
    BEGIN
        IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
        BEGIN
            SET @Temp = @Temp + @DailyWorkTime
        END
        --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
        ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
        BEGIN
            SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
        END

        ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
        BEGIN
            SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
        END
        --IF it starts and finishes in the same date
        ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
        BEGIN
            SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
        END
    END
    SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END

-- Return the result of the function
IF @Temp<0
BEGIN
    SET @Temp=0
END
	
RETURN @Temp - (case when  DATENAME(WEEKDAY,@FinishDate)=('Samedi') and datepart(hh,@FinishDate)< (select datepart(hh,close_time) FROM [TYPE].[dbo].[hours] where work_day='Saturday')
 then
(((select count (*)FROM [TYPE].[dbo].[Calendar] where jour_n='samedi' and cast([CalendarDate] as date) between cast(@StartDate as date) and cast(@FinishDate as date) )*120 )-120)+(select sum(cast(r.hour_h as int)) as hour_hol from (select jour, count(*) as numb_h, case when jour='Samedi' then '6' else '8' end as hour_h FROM [TYPE].[dbo].[holidays] where cast(holiday as date ) between cast(@StartDate as date) and cast(@FinishDate as date) group by jour)r)*60
else 
((select count (*)FROM [TYPE].[dbo].[Calendar] where jour_n='samedi' and cast([CalendarDate] as date) between cast(@StartDate as date) and cast(@FinishDate as date) )*120 )+(select sum(cast(r.hour_h as int)) as hour_hol from (select jour, count(*) as numb_h, case when jour='Samedi' then '6' else '8' end as hour_h FROM [TYPE].[dbo].[holidays] where cast(holiday as date ) between cast(@StartDate as date) and cast(@FinishDate as date) group by jour)r)*60
end)END

the problem when i excute this function like :
SELECT [dbo].[WorkTime] (
'06-04-2021 16:00:00'
, '29-05-2021 07:10:00')
GO
it return null

and for other date it turn perfectly:
SELECT [dbo].[WorkTime] (
'02-04-2021 16:00:00'
, '10-04-2021 07:10:00')
GO
please can help me

when i try i get this ERROR

Because the varchar column definition have no length so defaulting

I corrected the script to varchar 200

Then I got the error

I think it's day being put into month place
Month is 1 to 12
But day can be 30

I mean

Input expected format
year month day

Data input
Year day month

Month cannot accept beyond 12

Day 25 coming into month