[SQL] Comment remplir une table avec tous les jours de l’année?
Lors de la formation ‘MS 10774 Querying Microsoft SQL Server 2012’ que je viens de suivre nous sommes tombés sur une requête SQL sympa.
Celle-ci permet de remplir automatiquement une table calendrier avec tous les jours de l’année en prenant en compte le fait que l’année soit bissextile ou non.
Cela peut être utile pour créer des tables de tests, de statistiques ou tout autre type de table dont je suis curieux de connaitre la fonction (n’hésitez pas à me donner des exemples en commentaire).
La requête SQL de remplissage de calendrier
-- Création de la table calendrier SET NOCOUNT ON; IF OBJECT_ID('HR.Calendrier') IS NOT NULL DROP TABLE HR.Calendrier; CREATE TABLE HR.Calendrier( calendrier_date DATE CONSTRAINT PK_Calendrier PRIMARY KEY ); -- remplis la table calendrier avec tous les jours de l’année. DECLARE @startdate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 1, 1), @enddate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31); WHILE @startdate <= @enddate BEGIN INSERT INTO HR.Calendrier (calendrier_date ) VALUES (@startdate); SET @startdate = DATEADD(DAY, 1, @startdate); END; SET NOCOUNT OFF; GO -- Affiche toutes les données de la table calendrier SELECT calendrier_date FROM HR.Calendrier; |
Les explications de cette requête SQL
La première partie de la requête ne sert qu’à créer la table calendrier (Attention, ici on commence par effacer la table calendrier si elle existe). Rien de bien compliqué, un DROP TABLE pour détruire la table si elle existe et un CREATE TABLE pour la créer avec ajout d’une clef primaire histoire de respecter les règles de base de données relationnelles.
SET NOCOUNT ON; IF OBJECT_ID('HR.Calendrier') IS NOT NULL DROP TABLE HR.Calendrier; CREATE TABLE HR.Calendrier( calendrier_date DATE CONSTRAINT PK_Calendrier PRIMARY KEY ); |
Voila, nous allons enfin parler de la partie qui vous intéresse vraiment (enfin je suppose si vous lisez cette page), le remplissage de la table avec les jours de l’année.
Donc on commence par déclarer deux variables date (Attention, le type date est un nouveau type apparaissant avec SQL SERVER 2012)
- startdate qui va être la date de début du calendrier
- enddate qui va être la date de fin de remplissage du calendrier
DATEFROMPARTS(YEAR(SYSDATETIME()), 1, 1) va nous permettre de récupérer la valeur de l’année courante (du système).
Puis nous allons faire une boucle d’insert tant que la date de début est inférieur ou égale à la date de fin (WHILE @startdate <= @enddate ). SET @startdate = DATEADD(DAY, 1, @startdate); va nous permettre d'incrémenter de 1 jour en 1 jour la variable startdate pour pouvoir à terme arrêter la boucle.
-- remplis la table calendrier avec tous les jours de l’année. DECLARE @startdate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 1, 1), @enddate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31); WHILE @startdate <= @enddate BEGIN INSERT INTO HR.Calendrier (calendrier_date ) VALUES (@startdate); SET @startdate = DATEADD(DAY, 1, @startdate); END; |
Et enfin, la dernière partie de la requête SQL, un simple SELECT pour vérifier que les données saisies sont correctes.
-- Affiche toutes les données de la table calendrier SELECT calendrier_date FROM HR.Calendrier; |
Vous serez peut être aussi intéressez pas l’article suivant :
[SQL] Comment convertir une date de type string en une date de type date
Excellente requete pour 2012. et c’est quoi l’equivalent ace 2008 R2 ?
la fonction DATEFROMPARTS n’existe pas de façon native sur 2008 R2
Bonsoir,
En utilisant current_timestamp, datediff et dateadd tu devrais pouvoir t’en sortir pour obtenir le même résultat que le datefromparts sous 2008 R2.
Si tu arrives a générer une requête qui fonctionne je veux bien voir ta solution.
Merci,
J’ouvre MySql et je tape la requête comme indiquée. Résultat :
DECLARE = Mot clé non reconnu
Jeton inattendu près de @stardate et @enddate
WHILE = Type d’énoncé non reconnu
END = Type d’énoncé non reconnu
Je n’ai qu’un mot à dire : Bravo…
Bonjour Bryce,
Merci pour ce commentaire, il n’est pas surprenant qu’un article de 2013 pour une requête SQL server 2012 ne soit pas compatible avec une base MYSQL.
Une recherche rapide sur internet vous aurez permis de corriger rapidement la requête pour mysql (cf https://dev.mysql.com/doc/refman/8.0/en/declare.html -> DECLARE is permitted only inside a BEGIN … END compound statement ).
Vous donnez d’ailleurs la solution vous meme « DECLARE = Mot clé non reconnu
Jeton inattendu près de @stardate et @enddate »
Bon, vu que les solutions proposées ne fonctionnent pas ou son tout simplement ultra lourdes pour un simple remplissage d’une table vite fait, voici ce que j’ai fait tout simplement en une seule ligne de PHP.
Il suffit de créer la table de votre choix avec le champ DATE et d’adapter la ligne suivante avec les valeurs de votre choix :
for($i=strtotime(« jan 1, 2023 »);$iquery(« INSERT INTO calendrier SET jour=' ».date(« Y-m-d »,$i). »‘ »);
Rebonjour Bryce,
Merci pour cette solution (mais qui malheureusement ne fonctionne pas. Pour vous citer ‘je n’ai qu’un mot à dire Bravo!’ 🙂 ), mais elle n’est pas en adéquation avec l’article. Le but là était de le faire en SQL (pour sql server 2012).
Votre solution me parait d’ailleurs bien plus lourde puisqu’elle nécessite la mise en place de la connexion à la bdd et vu la requete un appel par jour ajouté, ce qui est loin d’être optimisé.
La solution proposée pour sql server 2012 pour remplir une table rapidement sans passer par du code est donc bien la bonne.
DECLARE@startdate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 1, 1),
@enddate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31);
WHILE @startdate <= @enddate BEGIN INSERT INTO HR.Calendrier (calendrier_date ) VALUES (@startdate); SET @startdate = DATEADD(DAY, 1, @startdate); END;
Voici une proposition de correction pour votre requête:
for( $i = strtotime( 'jan 1, 2023' ) ; $i < strtotime( 'jan 1, 2024' ) ; $i=strtotime( date( 'Y-m-d' , $i ) . ' +1 day' )){ echo ("'INSERT INTO calendrier SET jour=' ".date( 'Y-m-d' , $i ). "'\r"); }cf https://www.developpez.net/forums/d930679/php/langage/boucle-for-date/
Vous pouvez la tester via https://onlinephp.io/