Table Calendrier

Je n’y coupe jamais. A chaque fois, je me pose les mêmes questions. Dans beaucoup de projets, j’ai besoin d’un table calendrier et je ne me rappelle jamais des fonctions de format de date ! J’ai toujours besoin de retourner sur le site MSDN pour vérifier la syntaxe de telle ou telle fonction de date. Cette fois ci, j’ai décidé de publier un exemple de table calendrier. Cette table n’est pas forcément la plus complète mais elle a le mérite de recenser tout de même pas mal d’informations intéressantes et d’être facile à implémenter dans un ETL ou un job d’alimentation.

Création de la table Calendrier

Voici le DDL de la table Calendrier.

CREATE TABLE [dbo].[Calendrier]
(
   [DateJour] [date] NOT NULL,
   [Jour] [int] NOT NULL,
   [Mois] [int] NOT NULL,
   [Annee] [int] NOT NULL,
   [Trimestre] [int] NOT NULL,
   [Semestre] [int] NOT NULL,
   [JourAnnee] [int] NOT NULL,
   [JourSemaine] [int] NOT NULL,
   [Semaine] [int] NOT NULL,
   [SemaineIso] [int] NOT NULL,
   [JourTexteLong] [varchar](50) NOT NULL,
   [JourTexteCourt] [varchar](50) NOT NULL,
   [MoisTexteLong] [varchar](50) NOT NULL,
   [MoisTexteCourt] [varchar](50) NOT NULL,
   [DateTexteLong] [varchar](50) NOT NULL,
   [MoisTexteAnnee] [varchar](50) NOT  NULL,
   [TrimestreTexte] [varchar](14) NOT NULL,
   [SemestreTexte] [varchar](13) NOT NULL
) ON [PRIMARY]

ALTER TABLE dbo.Calendrier ADD CONSTRAINT PK_Calendrier
PRIMARY KEY CLUSTERED (DateJour ASC)

Insérer les données

Avant d’insérer les données, il faut tout d’abord définir une date de début et une date de fin.
Le script utilise la récursivité d’une CTE afin de sélectionner toutes les dates entre l’intervalle de début et de fin. (N’oubliez pas de fixer l’indicateur MAXRECURSION 0 à la fin de la requête)

DECLARE @DATEDEBUT DATE
DECLARE @DATEFIN DATE
SET @DATEDEBUT = '20100101'
SET @DATEFIN = '20141231'

;WITH mycte AS
(
   SELECT @DATEDEBUT DateJour
   UNION ALL
   SELECT DATEADD(DAY, 1, DateJour)
   FROM mycte
   WHERE DATEADD(DAY, 1, DateJour) < @DATEFIN
)
INSERT INTO [dbo].[Calendrier]
(    [DateJour]
   , [Jour]
   , [Mois]
   , [Annee]
   , [Trimestre]
   , [Semestre]
   , [JourAnnee]
   , [JourSemaine]
   , [Semaine]
   , [SemaineIso]
   , [JourTexteLong]
   , [JourTexteCourt]
   , [MoisTexteLong]
   , [MoisTexteCourt]
   , [DateTexteLong]
   , [MoisTexteAnnee]
   , [TrimestreTexte]
   , [SemestreTexte])
SELECT DateJour
   , DATEPART(DAY, DateJour) as Jour
   , DATEPART(MONTH, DateJour) as Mois
   , DATEPART(YEAR, DateJour) as Annee
   , DATEPART(QUARTER, DateJour) as Trimestre
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN 1 ELSE 2 END as Semestre
   , DATEPART(DAYOFYEAR, DateJour) as JourAnnee
   , DATEPART(WEEKDAY, DateJour) as JourSemaine
   , DATEPART(WEEK, DateJour) as Semaine
   , DATEPART(ISO_WEEK, DateJour) as SemaineIso
   , FORMAT(DateJour, 'dddd', 'fr-fr') as JourTexteLong
   , FORMAT(DateJour, 'ddd', 'fr-fr') as JourTexteCourt
   , FORMAT(DateJour, 'MMMM', 'fr-fr') as MoisTexteLong
   , FORMAT(DateJour, 'MMM', 'fr-fr') as MoisTexteCourt
   , FORMAT(DateJour, 'dd MMMM yyyy', 'fr-fr') as DateTexteLong
   , FORMAT(DateJour, 'MMMM yyyy', 'fr-fr') as MoisTexteAnnee
   , CASE WHEN DATEPART(QUARTER, DateJour) = 1 THEN '1er trimestre' ELSE CAST(DATEPART(QUARTER, DateJour) AS CHAR(1)) + 'ème trimestre' END as TrimestreTexte
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN '1er semestre' ELSE '2ème semestre' END as SemestreTexte
FROM mycte
OPTION (MAXRECURSION 0)

1er jour de la semaine

En fonction de l’environnement, le premier jour de la semaine n’est pas forcément le lundi. Mais pas de panique, il suffit de jouer avec cette fonction de session pour configurer le premier jour de la semaine. Plus d’information sur le site MSDN.

SET DATEFIRST 1

Insérer les nouvelles données


SET DATEFIRST 1

DECLARE @DATEDEBUT DATE
DECLARE @DATEFIN DATE
SET @DATEDEBUT = '20100101'
SET @DATEFIN = GETDATE()

;WITH mycte AS
(
   SELECT @DATEDEBUT DateJour
   UNION ALL
   SELECT DATEADD(DAY, 1, DateJour)
   FROM mycte
   WHERE DATEADD(DAY, 1, DateJour) < @DATEFIN
)
INSERT INTO [dbo].[Calendrier]
(    [DateJour]
   , [Jour]
   , [Mois]
   , [Annee]
   , [Trimestre]
   , [Semestre]
   , [JourAnnee]
   , [JourSemaine]
   , [Semaine]
   , [SemaineIso]
   , [JourTexteLong]
   , [JourTexteCourt]
   , [MoisTexteLong]
   , [MoisTexteCourt]
   , [DateTexteLong]
   , [MoisTexteAnnee]
   , [TrimestreTexte]
   , [SemestreTexte])
SELECT DateJour
   , DATEPART(DAY, DateJour) as Jour
   , DATEPART(MONTH, DateJour) as Mois
   , DATEPART(YEAR, DateJour) as Annee
   , DATEPART(QUARTER, DateJour) as Trimestre
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN 1 ELSE 2 END as Semestre
   , DATEPART(DAYOFYEAR, DateJour) as JourAnnee
   , DATEPART(WEEKDAY, DateJour) as JourSemaine
   , DATEPART(WEEK, DateJour) as Semaine
   , DATEPART(ISO_WEEK, DateJour) as SemaineIso
   , FORMAT(DateJour, 'dddd', 'fr-fr') as JourTexteLong
   , FORMAT(DateJour, 'ddd', 'fr-fr') as JourTexteCourt
   , FORMAT(DateJour, 'MMMM', 'fr-fr') as MoisTexteLong
   , FORMAT(DateJour, 'MMM', 'fr-fr') as MoisTexteCourt
   , FORMAT(DateJour, 'dd MMMM yyyy', 'fr-fr') as DateTexteLong
   , FORMAT(DateJour, 'MMMM yyyy', 'fr-fr') as MoisTexteAnnee
   , CASE WHEN DATEPART(QUARTER, DateJour) = 1 THEN '1er trimestre' ELSE CAST(DATEPART(QUARTER, DateJour) AS CHAR(1)) + 'ème trimestre' END as TrimestreTexte
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN '1er semestre' ELSE '2ème semestre' END as SemestreTexte
FROM mycte
WHERE NOT EXISTS (SELECT * FROM dbo.Calendrier c WHERE mycte.DateJour = c.DateJour)
OPTION (MAXRECURSION 0)

Conclusion

Table calendrier

SELECT sur la table calendrier

Bien évidemment cette table n’est pas exhaustive, n’hésitez donc pas à me proposer vos idées d’amélioration dans les commentaires.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Time limit is exhausted. Please reload CAPTCHA.