1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
|
CREATE FUNCTION datediff_ouvre
(
@F_DATE AS datetime,
@L_DATE AS datetime
)
returns integer
AS
begin
Declare @NB integer
Declare @SEC integer
Declare @SEC_DEB integer
Declare @SEC_FIN integer
Declare @F_DATE_T datetime
Declare @L_DATE_T datetime
SET @F_DATE_T = dateadd(day,0,convert(varchar,@F_DATE,103)) -- Enlève les heures à @F_DATE
SET @L_DATE_T = dateadd(day,0,convert(varchar,@L_DATE,103)) -- Enlève les heures à @L_DATE
SET @SEC_DEB = datediff(second,@F_DATE,dateadd(hour,19,@F_DATE_T))
SET @SEC_FIN = datediff(second,dateadd(hour,8,@L_DATE_T),@L_DATE)
SET @NB = 0
while @F_DATE_T != @L_DATE_T
BEGIN
IF datepart(dw, @F_DATE_T) NOT IN (6,7)
SET @NB = @NB + 1
SET @F_DATE_T = dateadd(dd,1,@F_DATE_T)
END
IF @NB > 0
SET @NB = @NB - 1
-- Réinitialisation des variables
SET @F_DATE_T = dateadd(day,0,convert(varchar,@F_DATE,103))
SET @L_DATE_T = dateadd(day,0,convert(varchar,@L_DATE,103))
If @F_DATE_T = @L_DATE_T
SET @SEC = datediff(second,@F_DATE,@L_DATE)
Else
SET @SEC = @NB*39600 + @SEC_DEB + @SEC_FIN
RETURN @SEC
end |
Partager