SQL - Format des dates

Article créé le	30 Nov 2020	Rédigé en version	SQL 2016

Comment convertir une date au format souhaité avec SQL ?

Guide détaillé

Voici une requête permettant de convertir la date du jour en différent format :

Format des dates

SELECT 
  CONVERT(VARCHAR,GETDATE(),100) as '100'
, CONVERT(VARCHAR,GETDATE(),101) as '101' 
, CONVERT(VARCHAR,GETDATE(),102) as '102'
, CONVERT(VARCHAR,GETDATE(),103) as '103'
, CONVERT(VARCHAR,GETDATE(),104) as '104'
, CONVERT(VARCHAR,GETDATE(),105) as '105'
, CONVERT(VARCHAR,GETDATE(),106) as '106'
, CONVERT(VARCHAR,GETDATE(),107) as '107'
, CONVERT(VARCHAR,GETDATE(),108) as '108'
, CONVERT(VARCHAR,GETDATE(),109) as '109'
, CONVERT(VARCHAR,GETDATE(),110) as '110'
, CONVERT(VARCHAR,GETDATE(),111) as '111'
, CONVERT(VARCHAR,GETDATE(),112) as '112'
, CONVERT(VARCHAR,GETDATE(),113) as '113'
, CONVERT(VARCHAR,GETDATE(),114) as '114'


SELECT 
  CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 114), ':', '') AS 'AAAAMMJJ_HHMMSSZZZ'
, REPLACE(CONVERT(VARCHAR, GETDATE(), 114), ':', '') AS 'HHMMSSZZZ'
, LEFT(CONVERT(VARCHAR,CONVERT(DATE,'01/03/1998'),112),6) as 'AAAAMM' 
, LEFT(CONVERT(VARCHAR,CONVERT(DATE, GETDATE() ),112),4) + '-' +SUBSTRING(CONVERT(VARCHAR,CONVERT(DATE, GETDATE() ),112),5,2)  as 'AAAA-MM' 
, REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),126),'-',''),'T','_'),':',''),'.','_') as 'YYYYMMDD_HHmmss_ccc'
, CONVERT(VARCHAR,GETDATE(),120) as '120'
, CONVERT(VARCHAR,GETDATE(),126) as '126'

Résultat pour la date du 30/11/2020 :

Et une requête permettant d'afficher des périodes :

Format des dates

SELECT  --*** Semaine 
   DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 1, 0) AS premier_jour_de_la_semaine_precedente 
   , DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) AS premier_jour_de_la_semaine_courante 
   , DATEADD(week, DATEDIFF(week, 0, GETDATE()) + 1, 0) AS premier_jour_de_la_semaine_prochaine 
   --- 
   , DATEADD(DAY, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)) AS dernier_jour_de_la_semaine_precedente 
   , DATEADD(DAY, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()) + 1, 0)) AS dernier_jour_de_la_semaine_courante 
   , DATEADD(DAY, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()) + 2, 0)) AS dernier_jour_de_la_semaine_prochaine 
   --*** Mois 
   , DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS premier_jour_du_mois_precedent 
   , DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS premier_jour_du_mois_courant  
   , DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) AS premier_jour_du_mois_prochain 
   --- 
   , DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS dernier_jour_du_mois_precedent 
   , DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) AS dernier_jour_du_mois_courant  
   , DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)) AS dernier_jour_du_mois_prochain 
   --*** Trimestre 
   , DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) - 1, 0) AS premier_jour_du_trimestre_precedent 
   , DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0) AS permier_jour_du_trimestre_courant 
   , DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) + 1, 0) AS premier_jour_du_trimestre_prochain 
   --- 
   , DATEADD(DAY, -1, DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0)) AS dernier_jour_du_trimestre_precedent 
   , DATEADD(DAY, -1, DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) + 1, 0)) AS dernier_jour_du_trimestre_courant 
   , DATEADD(DAY, -1, DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) + 2, 0)) AS dernier_jour_du_trimestre_prochain 
   --*** Semestre 
   , DATEADD(MONTH, ((DATEDIFF(quarter, 0, GETDATE()) / 2) * 6) - 6, 0) AS premier_jour_du_semestre_precedent 
   , DATEADD(MONTH, (DATEDIFF(quarter, 0, GETDATE()) / 2) * 6, 0) AS premier_jour_du_semestre_courant 
   , DATEADD(MONTH, ((DATEDIFF(quarter, 0, GETDATE()) / 2) * 6) + 6, 0) AS premier_jour_du_semestre_prochain 
   --- 
   , DATEADD(DAY, -1, DATEADD(MONTH, (DATEDIFF(quarter, 0, GETDATE()) / 2) * 6, 0)) AS dernier_jour_du_semestre_precedent 
   , DATEADD(DAY, -1, DATEADD(MONTH, ((DATEDIFF(quarter, 0, GETDATE()) / 2) * 6) + 6, 0)) AS dernier_jour_du_semestre_courant 
   , DATEADD(DAY, -1, DATEADD(MONTH, ((DATEDIFF(quarter, 0, GETDATE()) / 2) * 6) + 12, 0)) AS dernier_jour_du_semestre_prochain 
   --*** Annee 
   , DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AS premier_jour_annee_precedente 
   , DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()), 0) AS premier_jour_annee_courante 
   , DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) AS premier_jour_annee_suivante 
   --- 
   , DATEADD(DAY, -1, DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()), 0)) AS dernier_jour_annee_precedente 
   , DATEADD(DAY, -1, DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) AS dernier_jour_annee_courante 
   , DATEADD(DAY, -1, DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()) + 2, 0)) AS dernier_jour_annee_suivante 
   --*** Siècle 
   , DATEADD(YEAR, -YEAR(GETDATE()) % 100, DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()), 0)) AS premier_jour_du_siecle_courant 
   , DATEADD(YEAR, -YEAR(GETDATE()) % 100 + 100, DATEADD(DAY, -1, DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()), 0))) AS dernier_jour_du_siecle_courant