Calcul 'complexe' sur dates basées sur conversion de formats différents

Version imprimable

1 pièce(s) jointe(s)

Bonjour

J'ai, du fait de la conception de la base sur laquelle je travaille (et je me demande quel peut être l'avantage de stocker des Dates sous un format CHAR):

-Des dates stockées au Format CHAR de la façon suivante: DDMMYYYY=>DATE1
-Des dates stockées au Format CHAR de la façon suivante: YYYYMMDD=>DATE2

Dans une requête, j'ai une colonne affichant une valeur NUMERIQUE (ENTIER) 'MAVALEUR'

Je voudrais comparer cette valeur à la différence EN NOMBRE DE SEMAINES TELLES QUE [DATE2-(DATE1-14 JOURS)]<=MAVALEUR

Je procède de la façon suivante pour la conversion (mais il y a peut-être mieux):

-Conversion de format CHAR DDMMYYY:

Code:

CONVERT(VARCHAR(15),CAST(STR(RIGHT(DATE1,4)+SUBSTRING(DATE1,3,2)+LEFT(DATE1,2),8,0)AS DATETIME),105)

J'obtiens une date au format DD-MM-YYYY

-Conversion de format CHAR YYYYMMDD:

Code:

CONVERT(VARCHAR(15),CAST(STR(DATE2,8,0)AS DATETIME),105)

J'obtiens une date au format DD-MM-YYYY

Pour ma requête j'essaye de faire -mais cela ne fonctionne pas:
Code:

1 2 WHERE (...) AND DATEDIFF(DAY,CONVERT(VARCHAR(15),CAST(STR(RIGHT(DATE1,4)+SUBSTRING(DATE1,3,2)+LEFT(DATE1,2),8,0)AS DATETIME),105)-14,CONVERT(VARCHAR(15),CAST(STR(DATE2,8,0)AS DATETIME),105)<>MAVALEUR
Un petit coup de main?

Comme je ne m'explique sûrement pas bien, j'ai joint la véritable requête

PS: en fait -en fait je voudrais que mon DATEDIFF soit différent de MAVALEUR +/- 1 en SEMAINES, en sus du reste, je ne sais comment traduire le +/- 1

Merci d'avance

Plusieurs choses. Le seul intérêt que je peux voir à stocker des dates dans des char, c'est lorsqu'on travaille sur des vieilles dates, SQL-Server commençant assez tard (18ème siècle, mais à vérifier).

Pour les conversions, j'utilise ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
With MesDates as
(
select '18102012' as [DDMMYYYY]
     , '20121018' as [YYYYMMDD]
)
select convert(datetime, stuff(stuff([DDMMYYYY], 5, 0, '/'), 3, 0, '/'), 103) as [DDMMYYYY_dt]
     , convert(datetime, [YYYYMMDD], 112)                                     as [YYYYMMDD_dt]
  from MesDates;
 
DDMMYYYY_dt             YYYYMMDD_dt
----------------------- -----------------------
2012-10-18 00:00:00.000 2012-10-18 00:00:00.000

YYYYMMDD ayant le bon goût d'être le format ISO, je pense que la conversion n'est même pas nécessaire.

Par contre, vous transformez vos chaîne en date, puis vous les retransformez en chaîne, puis vous appliquer des fonctions de date dessus.
Voilà aussi pourquoi les gens stocke des dates dans des varchar, ils ne comprennent pas ce qu'est une date !

Je reprends votre requête et la réécrit quelque peu :

Code:

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
    SELECT DISTINCT
           IDSJR.NOSYGMA
         , IDPAT.NOMNAISS
         , IDPAT.PRENOM
         , IDSJR.NBRE_SEMAINES_AMENORRHEE
         , CONVERT(DATETIME, STUFF(STUFF(IDSJR.DATE_DERNIERES_REGLES, 5, 0, '/'), 3, 0, '/'), 103) AS DDR
         , CONVERT(DATETIME, IDACTE.DATE_ACTE, 112) AS DATE_ACTE
         , IDACTE.CODACTE
         , IDACTE.LIBACTE
         , IDACTE.ACTIVITE
      FROM IDSJR
INNER JOIN IDPAT  ON IDPAT.IUPATM       = IDSJR.IUPATM
INNER JOIN IDACTE ON IDACTE.IUHSPPMSI   = IDSJR.IUHSPPMSI
                 AND IDACTE.NUMOSJRPMSI = IDSJR.NUMOSJRPMSI
     WHERE IDSJR.DATSRTSJR >= '20120101'
       AND IDSJR.DATSRTSJR <  '20130101'
       AND IDSJR.IUUFRM     = '0092'
       AND IDACTE.ACTIVITE  = '1'
       AND IDSJR.CODCMDSJR  = '14'
       AND IDACTE.CODACTE IN ('JMPA006','JQGA002','JQGA003','JQGA004','JQGA005','JQGD001','JQGD002','JQGD003','JQGD004','JQGD005',
                              'JQGD007','JQGD008','JQGD010','JQGD012','JQGD013','JJFA001','JJFC001','JJJA002','JJJC002','JJLJ001',
                              'JJPA001','JJPC001','JNJD001','JNJD002','JNJP001','JNMD001','JQGA001','JQGD014')
       AND DATEDIFF(WEEK, CONVERT(DATETIME, STUFF(STUFF(IDSJR.DATE_DERNIERES_REGLES, 5, 0, '/'), 3, 0, '/'), 103) - 14
                        , CONVERT(DATETIME, IDACTE.DATE_ACTE, 112)
                    ) <> IDSJR.NBRE_SEMAINES_AMENORRHEE
  ORDER BY IDPAT.NOMNAISS ASC;

Pour le dernier filtre, faite quand même des tests.

Merci de cette réponse rapide

Je ne connaissais pas cette Fonction (STUFF) et vais y jeter un coup d'oeil

Pour les formats de date, c'est quand même assez compliqué (pour un non-informaticien, ce que je suis) mais si votre remarque
Code:

1 2 Par contre, vous transformez vos chaîne en date, puis vous les retransformez en chaîne, puis vous appliquer des fonctions de date dessus. Voilà aussi pourquoi les gens stocke des dates dans des varchar, ils ne comprennent pas ce qu'est une date !
s'applique aux informaticiens ayant conçu la base, effectivement cela pose question
:zoubi:

Code:

Par contre, vous transformez vos chaîne en date, puis vous les retransformez en chaîne, puis vous appliquer des fonctions de date dessus

En effet; je trouve cela un peu bizarre, mais je ne vois pas comment faire autrement; du reste cela doit être gourmand en ressources je suppose

Enfin, malheureusement j'ai un petit message d'erreur:
Code:

1 2 Serveur*: Msg 242, Niveau 16, État 3, Ligne 1 La conversion d'un type de données CHAR en type DATETIME a donné une valeur hors des limites des valeurs de date et d'heure.
Merci à vous

19/10/2012, 07h36
iberserk
Citation:

Le DATETIME ne gère pas les dates avant le premier janvier 1753 et après le 31/12/9999...

pour vous en persuader:
Code:

1 2 3 4 5 select CAST('10000101' AS DATETIME) select CAST('10000101' AS SMALLDATETIME) select CAST('10000101' AS DATE) select CAST('10000101' AS DATETIME2(2))
Seules les deux dernières requètes aboutiront...

Vous ne précisez pas la version de SQL SERVER

que donne sceci:
Code:

1 2 SELECT MIN(CAST(DATE2 AS DATE)) FROM MESDATES

Bonjour

Version SQL: 8.00.194

Pour: (en remplaçant par les bons déterminants de ma base)
Code:

1 2 SELECT MIN(CAST(DATE2 AS DATE)) FROM MESDATES
Code:

1 2 3 L'opération minimum aggregate ne peut pas prendre le type de données void type comme argument. Serveur*: Msg 243, Niveau 16, État 1, Ligne 1 Le type DATE n'est pas un type défini par le système.
Pour

Code:

SELECT CAST('10000101' AS DATE)

Code:

Le type DATE n'est pas un type défini par le système.

Citation:

Envoyé par Dr_No

Enfin, malheureusement j'ai un petit message d'erreur:

Citation:

Serveur*: Msg 242, Niveau 16, État 3, Ligne 1
La conversion d'un type de données CHAR en type DATETIME a donné une valeur hors des limites des valeurs de date et d'heure.

Là pas de miracle, c'est que toutes les données ne sont pas structurées comme vous le pensez.

Il faut analyser le contenu de vos tables :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select min(DATE_ACTE) as DATE_ACTE_min , max(DATE_ACTE) as DATE_ACTE_max from IDACTE; select DATE_ACTE from IDACTE where isdate(DATE_ACTE) = 0; select min(RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2)) as DATE_DERNIERES_REGLES_min , max(RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2)) as DATE_DERNIERES_REGLES_max from IDSJR; select RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2) as DATE_DERNIERES_REGLES from IDSJR where ISDATE(RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2)) = 0;

Code:

1
2
3
SELECT min(DATE_ACTE) AS DATE_ACTE_min
     , max(DATE_ACTE) AS DATE_ACTE_max
  FROM IDACTE;

Réponse:
DATE_ACTEmin => Rien
DATE_ACTE_max=20120905

Code:

1
2
3
SELECT DATE_ACTE
  FROM IDACTE
 WHERE isdate(DATE_ACTE) = 0;

Réponse:
DATE_ACTE: Toutes les valeurs (201420 ligne) =NULL

Code:

1
2
3
SELECT min(RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2)) AS DATE_DERNIERES_REGLES_min
     , max(RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2)) AS DATE_DERNIERES_REGLES_max
  FROM IDSJR;

Réponse:
DATE_DERNIERES_REGLES_min: RIEN
DATE_DERNIERES_REGMES_max: 20120801

Code:

1
2
3
SELECT RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2) AS DATE_DERNIERES_REGLES
  FROM IDSJR
 WHERE ISDATE(RIGHT(DATE_DERNIERES_REGLES, 4) + SUBSTRING(DATE_DERNIERES_REGLES, 3, 2) + LEFT(DATE_DERNIERES_REGLES, 2)) = 0;

Réponse:

Soit NULL
Soit Pas de valeur

19/10/2012, 12h16
SQLpro

Citation:

Envoyé par Waldar

Plusieurs choses. Le seul intérêt que je peux voir à stocker des dates dans des char, c'est lorsqu'on travaille sur des vieilles dates, SQL-Server commençant assez tard (18ème siècle, mais à vérifier).

Non, ça n 'a pas de sens... En effet entre le 4 octobre 1582 et le 31 janvier 1917 chaque pays avait des dates différentes. Ceci est du à l’adoption de la réforme du calendrier grégorien.
Extrait de mon livre sur SQL :
"
Réforme du calendrier julien :
Au cours de l’Histoire et compte tenu de la précision accrue des calculs astronomiques, on constate que le calendrier julien souffre d’un manque de précision : la durée de l’année julienne est trop longue d’un peu plus de 11 minutes (365,25 jours au lieu de 365,2422) et, au fil du temps, le calendrier finit par retarder de plusieurs jours. C’est le pape Grégoire XIII qui entreprend de réformer le calendrier. Pour cela, 10 jours furent supprimés de l’année 1582, où le 4 octobre fut immédiatement suivi par le 15 octobre. Pour éviter de nouvelles dérives, la surévaluation de l’année julienne fut corrigée par la suppression de 3 jours tous les 400 ans. On ignore donc la règle des années bissextiles les années séculaires, sauf pour celles qui sont divisibles par 400.
On a donc 97 années bissextiles par période de 400 ans et la durée moyenne d’une année grégorienne est 365 + 97/100, c’est-à-dire 365,2425 jours.
Cette réforme nécessaire n’a pas été adoptée à la même date dans tous les pays, par exemple :
• L’Italie, l’Espagne, le Portugal et la Pologne sont passés du 4 octobre 1582 au 15 octobre 1582.
• La France (sauf l’Alsace et la Lorraine) est passée du 9 décembre 1582 au 20 décembre 1582.
• Le Luxembourg est passé du 14 décembre 1582 au 25 décembre 1582.
• La Belgique (alors province des Pays-Bas) est passée du 21 décembre 1582 au 1er janvier 1583.
• Le Valais Suisse est passé du 28 février 1655 au 11 mars 1655.
• L’Alsace est passée du 4 février 1682 au 16 février 1682.
• Zurich, Berne, Bâle et Genève sont passés du 31 décembre 1700 au 12 janvier 1701.
• L’Angleterre est passée du 2 septembre 1752 au 14 septembre 1752.
• La Lorraine est passée du 16 février 1760 au 28 février 1760.
• L’URSS est passée du 31 janvier 1917 au 14 février 1917.
"

Lorsque l'on doit avoir des dates dans cette période il faut créer des caledrier géolocalisés car une date dans un pays n'(est pas la même dans l'autre.

SQL Server ayant été créé par des ricains, la date minimale est celle du 14 septembre 1752, date de l'adoption de la réforme par les anglais...

A +
19/10/2012, 13h28
Dr_No

Merci à tous

Tous ces formats de conversion, de dates etc, c'est quand même bien complexe

Pour les formats de date, j'avais bien lu l'article de F.Brouard sur ce site, mais cela devient vite complexe (non pas que ce soit hors de ma compréhension, mais ce n'est pas le coeur de mon métier et comprendre est une chose, intégrer couramment ces données en est une autre)

Pour ce qui est de la condition de ma requête, j'ai trouvé la solution -ou plutôt elle m'a été soufflée par l'informaticien de mon centre hospitalier, ce qui n'est pas coutume :mouarf:

Code:

AND DATEDIFF(WEEK,DATEADD (DAY,-14,DBO.PLUS_CONVERT_DATE(SUBSTRING (MADATEFORMAT,5,4)+SUBSTRING (IDSJR.DATE_DERNIERES_REGLES,3,2)+SUBSTRING(IDSJR.DATE_DERNIERES_REGLES,1,2))), DBO.PLUS_CONVERT_DATE(IDACTE.DATE_ACTE))<>NBRE_SEMAINES_AMENORRHEE