[SQL] Création d'un calendrier

La génération automatique d'un calendrier peut s'avérer très utile dans le cadre de la mise en place d'un Datawarehouse notamment pour la dimension Temps, incontournable à tout DW qui se respecte.

Voici un script SQL qui permet de générer (via CTE) un calendrier en paramétrant la date de début et la date de fin de celui-ci.

'set datefirst' permet de définir quel est le premier jour de la semaine. 1 pour Lundi, 2 pour Mardi, etc...

'set language' permet de définir la langue du calendrier notamment pour les noms de jour/ mois (Monday/Lundi, January/Janvier)

--DEBUT DU SCRIPT

--set language 'english' => FORMAT Date debut et fin du calendrier = YYYY-MM-DD

--set language 'french' => FORMAT Date début et fin du calendrier = YYYY-DD-MM

set language 'english';

set datefirst 1;

WITH

myCTE as

                (

                SELECT Cast ('2012-01-01' as DateTime) Date

                UNION  ALL

                SELECT Date + 1 FROM myCTE WHERE Date + 1 < = Cast ('2012-12-31' as DateTime)

                )

SELECT

                CAST(convert(Varchar(10),date,112) as integer) AS CAL_ValueDate

                ,Date AS CAL_DateName

                ,CONVERT(VarChar(12),date,113) AS CAL_Year_Datetime

                ,CAST(cast(YEAR (date) AS varchar(4))+'0101' as datetime) AS CAL_Year_Datetime

                ,YEAR (date) AS CAL_Year_Name

                ,cast(cast(YEAR (date) AS varchar(4)) + '0' + cast(Ceiling(Month(date)/6.0)as varchar(1)) +'01' as datetime) AS CAL_Semester_Datetime

                ,Ceiling(Month(date)/6.0) AS CAL_SemesterName

                ,CAST(cast(YEAR (date) AS varchar(4))+right('0'+cast(((DatePart ( qq, date)-1)*3)+1 AS varchar(2)),2)+'01' as datetime) AS CAL_Trimester_Datetime

                ,DatePart (qq, date) AS CAL_Trimester_Name

                ,CAST(cast(YEAR (date) AS varchar(4))+right('0'+cast(MONTH (date) AS varchar(2)),2)+'01' as datetime) AS CAL_Month_Datetime

                ,MONTH (date) AS CAL_Month_Number_Year

                ,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1  AS CAL_Month_Number_Of_Trimester

                ,DateName (mm, date) AS CAL_Month_Name_FR

                ,LEFT( DateName (mm, date), 3) AS CAL_Month_Name_Abbreviation_FR

               

                ,DatePart (wk, Date) AS CAL_Week_Number_Of_Year

                ,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 AS CAL_Week_Number_Of_Trimester

                ,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 AS CAL_Week_Number_Of_Month

               

                ,DatePart (dy, date) AS CAL_Day_Number_Of_Year

                ,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 AS CAL_Day_Number_Of_Trimester

                ,DAY (date)  AS CAL_Day_Number_Of_Month

               

                ,datepart(dw,date) AS CAL_Day_Number_Of_Week

                ,DateName (dw, date) AS CAL_Day_Name

                ,LEFT(DateName (dw, date), 3) as CAL_Day_Name_Abbreviation 

FROM myCTE

OPTION (MAXRECURSION 0)