Cette fonction permet de calculer une différence en minutes entre deux dates/heures, dans le cadre (le plus souvent) d'une entreprise.
La fonction donne la différence et déduit :
- les nuits en dehors de la plage horaire
- les samedis et dimanches
- les jours fériés
Pour l'utiliser :
dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE(Date1, Date2) => Fait la différence Date2 - Date1 avec le traitement cité ci-dessus
Exemple :
SELECT dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE('14/05/2009 12:00:00', '14/05/2009 13:05:00') AS 'Différence (en minutes)'
Ou :
PRINT dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE ('07/05/2009 17:30:00', '11/05/2009 08:05:00')
Pour le calcul dynamique des jours fériés, je me suis basé sur ce script :
http://www.sqlfr.com/codes/ORACLE-SQL-SERVER-2K-FONCTION-RETOURNANT-STATUS-OUVRE_27798.aspx
Source / Exemple :
--====================================================================--
-- Ensemble de fonctions de calculs de date avec jours fériés
--====================================================================--
-----------------------------------------------------------------------
-- FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE
-- calcule la différence en minutes entre deux dates
-- en fonction des horaires de l'ENTREPRISE :
-- ici : 08h00 - 18h00
--
-- Déduit les nuits entre 18h00 et 08h00 le lendemain
-- ainsi que les week-ends et les jours fériés
-----------------------------------------------------------------------
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE')
DROP FUNCTION dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE
GO
CREATE FUNCTION FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE (@Date1 DATETIME, @Date2 DATETIME)
RETURNS INT AS
BEGIN
DECLARE @DateCreCalcul DATETIME
DECLARE @NbJourneesNonTravaillees INT
DECLARE @NbNuits INT
SET @DateCreCalcul = @Date1
-- Si Date1 inférieure à l'heure d'ouverture
IF DATEPART(hour, @Date1) < 8
BEGIN
SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @Date1) + 8, @Date1)
SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
END
-- Si Date1 supérieure à l'heure de fermeture
IF DATEPART(hour, @Date1) >= 18 BEGIN
SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @Date1) + 18, @Date1)
SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
END
-- Si Date1 est un dimanche, on ajoute 1 jour et on commence à l'heure d'ouverture
IF DatePart(weekday, @Date1) = 7 BEGIN
SET @DateCreCalcul = DATEADD(day, 1, @DateCreCalcul)
SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @DateCreCalcul) + 8, @DateCreCalcul)
SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
END
-- Si Date1 est un samedi, on ajoute 2 jours et on commence à l'heure d'ouverture
IF DatePart(weekday, @Date1) = 6 BEGIN
SET @DateCreCalcul = DATEADD(day, 2, @DateCreCalcul)
SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @DateCreCalcul) + 8, @DateCreCalcul)
SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
END
SET @NbJourneesNonTravaillees = 0
SET @NbNuits = DATEDIFF(day, @DateCreCalcul, @Date2)
-- Si les 2 dates sont à des jours différents, on cherche le nombre de jours NON travaillés entre les 2 dates
DECLARE @Compteur_NbNuits int;
SET @Compteur_NbNuits = 0;
-- Dans une boucle, on balaye tous les jours entre les 2 dates pour compter le nombre de jours non travaillés
WHILE @Compteur_NbNuits < @NbNuits
BEGIN
SET @Compteur_NbNuits = @Compteur_NbNuits + 1;
IF dbo.FN_JourTravailleFerie(DATEADD(day, @NbJourneesNonTravaillees + 1, @DateCreCalcul), 1) <> 'X'
BEGIN
SET @NbJourneesNonTravaillees = @NbJourneesNonTravaillees + 1
END
END
-- On calcule la différence en minutes et on retire :
-- - la durée des des nuits en minutes
-- - la durée des journées non travaillées en minutes
RETURN datediff(minute, @DateCreCalcul, @Date2) - 840*@NbNuits - 600*@NbJourneesNonTravaillees
END
GO
-----------------------------------------------------------------------
-- Ensembles de fonctions qui déterminent si un jour est travaillé,
-- week-end ou férié
--
-- Entrée : date à tester,
-- Considérer le samedi comme un jour férié ?
-- Par défaut les samedi sont considérés comme fériés
-- 0 : est considéré travaillé
-- 1 : est considéré férié
--
-- Sortie : chaine de caractère
-- X : jour travaillé
-- WE : Week-end
-- JF : Jour férié
--
-- Exemples d'appel de la fonction :
-- dbo.FN_JourTravailleFerie(getdate(), 1)
-- dbo.FN_JourTravailleFerie('21/04/2009', 1)
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Mini' AND type = 'FN') DROP FUNCTION Mini
GO
CREATE FUNCTION Mini(@a float, @b float) returns float
AS
BEGIN
DECLARE @f float
if @a < @b
SET @f = @a
ELSE
SET @f = @b
return @f
END
GO
-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Maxi' AND type = 'FN') DROP FUNCTION Maxi
GO
CREATE FUNCTION Maxi(@a float, @b float) returns float
AS
BEGIN
DECLARE @f float
IF @a > @b
SET @f = @a
ELSE
SET @f = @b
return @f
END
GO
-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'IsCorrectDate' AND type = 'FN') DROP FUNCTION IsCorrectDate
GO
CREATE FUNCTION IsCorrectDate (@JJ integer, @MM integer, @AAAA integer) returns integer
AS
BEGIN
DECLARE @FlagReturn integer
-- Hors plage
IF @JJ < 1 Or @JJ > 31 Or @MM < 1 Or @MM > 12
BEGIN
SET @FlagReturn = 0
END
ELSE
BEGIN
-- Mois de 30 jours
IF @JJ = 31 AND (@MM = 4 Or @MM = 6 Or @MM = 9 Or @MM = 11)
BEGIN
SET @FlagReturn = 0
END
ELSE
BEGIN
-- Mois de février
IF @MM = 2
BEGIN
IF @JJ <= 28
BEGIN
SET @FlagReturn = 1
END
ELSE
BEGIN
IF @JJ > 29
BEGIN
SET @FlagReturn = 0
END
ELSE
BEGIN
IF Not (@JJ = 29 AND ((@AAAA % 4 = 0 AND @AAAA % 100 <> 0) Or @AAAA % 400 = 0))
BEGIN
SET @FlagReturn = 0
END
ELSE
BEGIN
SET @FlagReturn = 1
END
END
END
END
ELSE -- mois autre que fevrier
BEGIN
SET @FlagReturn = 1
END
END
END
Return @FlagReturn
END
GO
-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'ConvertDate' AND type = 'FN') DROP FUNCTION ConvertDate
GO
CREATE FUNCTION ConvertDate (@dtDate DateTime) returns varchar(4)
AS
BEGIN
DECLARE
@iJour integer,
@iMois integer,
@sJour varchar(2),
@sMois varchar(2),
@sResult varchar(4)
SET @iJour = datepart(dd, @dtDate)
SET @iMois = datepart(mm, @dtDate)
SET @sJour = CAST(@iJour AS VARCHAR)
SET @sMois = CAST(@iMois AS VARCHAR)
IF len(@sJour) = 1
BEGIN
SET @sJour = '0' + @sJour
END
IF len(@sMois) = 1
BEGIN
SET @sMois = '0' + @sMois
END
SET @sResult = @sJour + @sMois
RETURN @sResult
END
GO
-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'FN_JourTravailleFerie' AND type = 'FN') DROP FUNCTION FN_JourTravailleFerie
GO
CREATE FUNCTION FN_JourTravailleFerie (@dtDate DateTime, @iSamediFerie integer = 1) returns varchar(2)
AS
BEGIN
-- Cette fonction indique si le jour passé en argument est férié.
-- Par défaut les samedi sont considérés comme fériés, mais le second
-- paramètre permet de changer cela.
DECLARE
@lgA bigint,
@lgMPaq bigint,
@lgJPaq bigint,
@lgTmp1 bigint,
@lgTmp2 bigint,
@lgTmp3 bigint,
@stDate varchar(10),
@dtPaq DateTime,
@stPaq varchar(10),
@blFerie integer,
@stType varchar(2)
SET @stType = 'X'
-- Dimanche (et samedi)
IF @iSamediFerie = 1
BEGIN
IF DatePart(weekday, @dtDate) = 7 OR DatePart(weekday, @dtDate) = 6
BEGIN
SET @blFerie = 1
END
ELSE
BEGIN
SET @blFerie = 0
END
END
ELSE
BEGIN
IF DatePart(weekday, @dtDate) = 7
BEGIN
SET @blFerie = 1
END
ELSE
BEGIN
SET @blFerie =0
END
END
IF @blFerie = 1
BEGIN
SET @stType = 'WE'
END
IF @blFerie = 0
BEGIN
SET @stDate = dbo.ConvertDate(@dtDate)
-- Jours fériés fixes (1er janvier, 1er mai, 8 mai, 14 juillet, ...)
IF @stDate IN ('0101','0105','0805','1407','1508','0111','1111','2512')
BEGIN
SET @blFerie = 1
END
END
IF @blFerie = 0
BEGIN
SET @lgA = Year(@dtDate)
SET @lgTmp1 = (19 * (@lgA % 19) + 24) % 30
SET @lgTmp2 = dbo.Mini(@lgTmp1, dbo.Maxi(28, @lgTmp1) - 1)
SET @lgTmp3 = 28 + @lgTmp2 - ((3 + @lgA - 1900 + ((@lgA - 1900) / 4) + @lgTmp2) % 7)
SET @lgMPaq = 3 + (@lgTmp3 / 32)
-- Remarque Le calcul du jour de pâques ne fonctionne pas toujours. L'arrondi est mauvais lorsqu'il y a une division : mettre un TRUNC(ladivision,0) pour garder la partie entière.
-- http://www.sqlfr.com/codes/ORACLE-SQL-SERVER-2K-FONCTION-RETOURNANT-STATUS-OUVRE_27798.aspx
-- SET @lgMPaq = 3 + round((@lgTmp3 / 32), 0)
IF (@lgTmp3 < 32)
BEGIN
SET @lgJPaq = @lgTmp3
END
ELSE
BEGIN
SET @lgJPaq = @lgTmp3 - 31
END
-- Construction de la date du dimanche de Pâques
SET @stPaq = CAST(@lgJPaq AS varchar) + '/' + CAST(@lgMPaq AS varchar) + '/' + CAST(@lgA AS varchar)
SET @dtPaq = CAST (@stPaq AS DateTime)
-- Jours fériés mobiles (lundi de pâques, ascension, lundi de pentecôte)
-- Pâques et Pentecôte non testés, puisqu'ils tombent tous les deux un dimanche.
IF (@dtDate = (@dtPaq + 1)) Or (@dtDate = (@dtPaq + 39)) Or (@dtDate = (@dtPaq + 50))
BEGIN
SET @blFerie = 1
END
END
IF @blFerie = 1 AND @stType = 'X'
BEGIN
SET @stType = 'JF'
END
RETURN @stType
END
GO
Conclusion :
L'heure pour Date1 est retravaillée pour coller à la plage horaire :
- si elle est inférieure à l'heure d'ouverture, on prend en compte l'heure d'ouverture
- si elle est supérieure à l'heure de fermeture, on prend en compte l'heure de fermeture
L'heure pour Date2 n'est retravaillée.
Remarque : j'ai développé cette fonction pour mon cas précis. Elle devra probablement être adaptée pour d'autres cas (horaires d'ouverture/fermeture par exemple) mais cela donne un départ, c'est pour ça que je la partage.
Vos commentaires sont les bienvenus.
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.