two - sql server date du jour sans heure




Calculer la différence entre 2 dates en SQL, à l'exclusion des jours de week-end (7)

D'un post précédent :

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)

Je voudrais construire une requête SQL qui calcule la différence entre 2 dates, sans compter les jours de week-end dans le résultat.

Est-il possible de formater les dates pour obtenir ce résultat? Par exemple pour la base de données Oracle:

select sysdate - creation_dttm from the_table

Il y a un problème avec @lightmania réponse je pense.

Utiliser des valeurs:

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;

Renvoyé 0 au lieu de 2 qu'il aurait dû renvoyer.


J'ai trouvé plusieurs des réponses sur ce sujet pour ne pas faire ce qu'ils prétendent. Après quelques expérimentations, tests et ajustements, j'ai ceci à contribuer.

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)

Test harnais inclus - vous pouvez simplement ajuster les deux dates et exécuter vos propres tests. Cela suppose que la différence entre deux dates de jour de semaine adjacentes est 1. Si votre pays utilise des jours différents pour signifier le week-end, vous devrez définir la base de dates en conséquence pour que votre "samedi" soit 7 et votre "dimanche" 1.


J'ai trouvé une autre façon de calculer la différence, en utilisant seulement SQL:

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

Voici un exemple:
Il y a quatre variables, les deux premières sont explicites, il suffit d'entrer la date au format AAAAMMJJ, la troisième est de définir le nombre de jours de travail normaux dans une semaine donnée, donc si un site fonctionne 6 jours par semaine, réglez-le à 6, cinq jours par semaine, entrez 5, etc. Enfin, DATE_SEQ_NORML_FACTOR doit être 1 lors de l'exécution sur Oracle. Ceci est pour aligner la date julienne à 1 le lundi, 2 le mardi, etc lors de l'application du MOD 7. Autre DB aura probablement des valeurs différentes entre 0 et 6, donc le tester avant de l'utiliser contre d'autres DB.

Voici les limites: 1. Cette formule suppose que le premier jour de la semaine est le lundi. 2. Cette formule suppose que tous les jours de la même semaine sont CONTINUS. 3. Cette formule ne fonctionnera que lorsque les deux dates impliquées dans le calcul tombe un jour de la semaine ou un jour de travail, par exemple. la "Date de début" un SAMEDI quand l'emplacement fonctionne seulement MON-FRI ne fonctionnera pas.

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

Vous devriez essayer avec une fonction:

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;

Résultat :

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

  • Calcule la différence en jours entre les deux dates
  • Calcule la différence entre les numéros de semaine et les numéros d'année, soustrait les numéros de semaine, puis multiplie le résultat par 2 pour calculer le nombre de jours non travaillés entre les deux dates. Si les numéros d'année sont différents calcul de 52 * différence d'année + numéro de semaine.

((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





date