SQL : Une date arrondie au Quart d'heure

 Voila ce que j'ai trouvé de plus simple pour arrondir une date au quart d'heure.

Dites-moi si vous avez mieux !

SELECT DATEADD(MINUTE, (DATEDIFF(MINUTE, CONVERT(DATE, DateEvent), DateEvent) / 15) * 15, CONVERT(DATETIME, CONVERT(DATE, DateEvent)))

1. On prend la date sans les heures : CONVERT(DATETIME, CONVERT(DATE, DateEvent))
2. On calcule la différence en minutes entre la date sans les heures CONVERT(DATE, DateEvent) et la date avec les heures DateEvent : (DATEDIFF(MINUTE, CONVERT(DATE, DateEvent), DateEvent)
3. En divisant le résultat qui est un entier par quinze : on bénéficie de la division entière et donc on a un nombre entier tout rond, qu'il suffit de remultiplier par quinze pour avoir un nombre de minutes arrondie au quart d'heure : (DATEDIFF(MINUTE, CONVERT(DATE, DateEvent), DateEvent) / 15) * 15
4. On ajoute ce nombre à la date sans les heures, celle du point n°1 :  DATEADD(MINUTE, (différence en minutes entre (date, date et heure) / 15) * 15, date): et on a l'heure arrondie au quart d'heure.

Enjoy !

Afficher les mois et jours de la semaine en texte

Le saviez vous ? SQL maintient la liste des noms des mois et jours de la semaine dans l'ensemble des langues qu'il gère ! (Surement pour pouvoir faire fonctionner la fonction FORMAT).

Mais cela peut être utile dans certaines "compositions de textes" d'avoir ces noms, alors voici comment j'extrais ces données de SQL Server.

Pour les mois :

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num_month, [value] As [Month]
FROM sys.syslanguages s CROSS APPLY string_split(months, ',')
---WHERE lcid = 1036  --- [name] = 'Français'
WHERE [name] = 'us_english'

Quelques explications :

• L'info est dans la colonne "months" de la table sys.syslanguages, mais cette colonne contient une chaine avec tous les mois séparés par des virgules.
• Donc j'utilise STRING_SPLIT pour couper la chaine en 12 lignes et CROSS APPLY pour faire en sorte que cela s'extrait correctement (sans passer par une variable).
• Le ROW_NUMBER() permet d'avoir le numéro du mois associé au nom extrait. (A noter le (SELECT NULL) qui permet de contourner le tri obligatoire de  cet opérateur !)
• Enfin le WHERE permet de choisir sa langue : Soit par le LCID (1036 pour le français par exemple) soit par son nom !

Pour information, les mois en abrégés sont aussi disponibles : dans la colonne "shortmonths".

Pour les jours de la semaine (c'est le même principe) : 

SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - datefirst + 14) % 7 + 1 AS Num_day, [value] As [day]
FROM sys.syslanguages s CROSS APPLY string_split(days, ',')
WHERE lcid = 1036  --- [name] = 'Français'
--WHERE [name] = 'us_english'

Quelques explications :

• L'info est dans la colonne "days" de la table sys.syslanguages, 
• STRING_SPLIT, CROSS APPLY : Tout pareil que ci-dessus.
• ATTENTION ici le ROW_NUMBER() prend en compte la colonne "datefirst" pour faire en sorte que les numéros obtenus permettent de trier les jours dans le bon ordre en fonction de la langue (En Anglais et d'autres langues c'est le Dimanche le premier jour de la semaine !!). On retire "datefirst" et on ajout 14 pour être certain d'avoir un nombre positif qui avec le modulo sera ramené entre 0 et 6 d'où le +1 final pour avoir un nombre entre 1 et 7 !!
• Ajustez le WHERE pour choisir la langue.

Enjoy !

Alternative : (??!)
Si on a le numéro du mois pour avoir le nom :

DECLARE @Month INT = 7   --- Juillet
SELECT FORMAT(CONVERT(DATE, '2000-' + FORMAT(@Month, '00') + '-01T00:00:00'), 'MMMM') AS [Month]

SQL Server : Jours de la semaine

 J'y ai passé plus de 5 minutes sur ce sujet (Et j'ai pas retrouvé mon post précédent :-( sur le sujet), c'est trop long, donc encore un petit mémo pour s'en rappeler :

Dans SQL Server les jours de la semaine sont renvoyés par la fonction DATEPART(WEEKDAY, @date)

La fonction renvoie un nombre de 1 à 7 qui correspond au numéro d'ordre du jour dans la semaine.

Le premier jour étant... paramétrable !   

....La galère commence !

SELECT @@DATEFIRST est le paramètre qui renvoie 1 si le premier jour est le lundi, 2 si c'est le mardi, ... (devinez la suite)

On peut changer cela avec un SET DATEFIRST x (Ou x vaut de 1 lundi à 7 dimanche)

Donc pour connaitre toujours de la même manière le jour de la semaine maintenant je fais :

SELECT CASE ((DATEPART(WEEKDAY, @date) + @@DATEFIRST -1 ) % 7)
WHEN 0 THEN 'Dimanche' WHEN 1 THEN 'Lundi' WHEN 2 THEN 'Mardi' WHEN 3 THEN 'Mercredi' .... WHEN 6 THEN 'Samedi' END

L'idée est de toujours retomber sur ses pattes en prenant un modulo 7, mais le modulo 7 renvoie de 0 à 6 !
En revanche WEEKDAY et @@DATEFIRST renvoient des nombres entre 1 et 7.
Et j'aimerais bien en profiter pour caler le résultat sur l'énumération l'énumération DayOfWeek de C# (Pour laquelle le Dimanche est un 0, le Lundi 1, Mardi 2, ... Samedi 6) 

C'est pour cela que j'ajoute -1 à la somme de @@DATEFIRST et du jour de la semaine a pour faire en sorte qu'un dimanche renvoie toujours 0, lundi renvoie toujours 1, ...

Enjoy ! 

SQL - Les Formats de date et heure

Vous galérez pas à trouver le bon format pour convertir les dates en texte dans SQL Server vous ?
Moi Oui !!
Du coup j'utilise la requête ci-dessous pour trouver le bon format en fonction de mes besoins :

DECLARE @date DATETIME
SET @date = GETDATE() --- la date que vous voulez !!
SELECT format, CONVERT(NVARCHAR(MAX), @date, format)
FROM (SELECT ROW_NUMBER() OVER(ORDER BY object_id) - 1 AS format
FROM sys.all_objects) x
WHERE format < 132 
  AND format NOT IN (15, 16, 17, 18, 19, 115, 116, 117, 118, 119, 122, 123, 124, 125, 128, 129) 
  AND (format < 26 OR format > 99 )


On compte le nombre de ligne de sys.all_objets ou de n'importe quelle table qui a plus de 132 lignes !!
Puis on exclus les ID non valides de la fonction convert
Puis on affiche toutes les conversions possibles.

La doc SQL SERVER de la fonction CONVERT est ici


Plus sexy avec une CTE récursive :
DECLARE @date DATETIME
SET @date = GETDATE()
;
WITH nombre (idx)
 AS (SELECT 0 AS idx
     UNION ALL SELECT idx + 1 FROM nombre WHERE idx < 131)
SELECT idx AS format, CONVERT(NVARCHAR(MAX), @date, idx) FROM nombre 
WHERE idx NOT IN (15, 16, 17, 18, 19, 115, 116, 117, 118, 119, 122, 123, 124, 125, 128, 129) 
  AND (idx < 26 OR idx> 99 )
OPTION(maxrecursion 132)

A noter le ; avant le WITH De la CTE
A noter aussi on utilise la clause OPTION pour augmenter la profondeur de récursion car ici on a besoin d'une profondeur connue de 132



Enjoy !

SQL : Premier jour du mois et dernier jour du mois

Voici ce que j'utilise pour trouver le premier jour du mois d'une date donnée :

DECLARE @dt DATE = '2017-02-17'

SELECT  @dt, DATEADD(DAY, 1 - DATEPART(DAY, @dt), @dt)
Ou
SELECT  @dt, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0)


Ce qui donne dans les deux cas :
17/02/2017     01/02/2017

Dans le premier exemple : on retire le nombre de jour moins 1 à la date en cours.
Dans le second : on ajoute à la date "0" la différence de mois entre "0" et la date fournie



Voici ce que j'utilise pour trouver le dernier jour du mois d'une date donnée :

DECLARE @dt DATE = '2017-02-17'
SELECT  @dt, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt) + 1, 0))


Ce qui donne  :
17/02/2017     28/02/2017

Explication : on ajoute un mois au premier jour du mois et on retire un jour.


MAJ 05/2021 : Sinon pour de le dernier jour du mois, il existe a maintenant (depuis SQL Server 2014!) la fonction EOMONTH().

On lui donne un jour dans le mois, elle renvoie le dernier jour du mois, simple, Merci SQL Server ! 

Enjoy !

SQL et c# Jour de la semaine

Pour avoir le jour de la semaine avec SQL Server il faut utiliser la fonction DATEPART avec le paramètre WEEKDAY :  SELECT DATEPART(WEEKDAY, GETDATE()) pour avoir le numéro du jour d'aujourd'hui. Le résultat est un nombre entre 1 et 7. Soit :

• 1 = Lundi
• 2 = Mardi
• 3 = Mercredi
• 4 = Jeudi
• 5 = Vendredi
• 6 = Samedi
• 7 = Dimanche

Attention le résultat est fonction de la variable @@DATEFIRST qui permet d'ajuster le nombre voulu pour le résultat.

Voir la doc SQL Server pour plus de détail ; ici

En C# la propriété DayOfWeek d'un type DATETIME renvoie une énumération qui une fois casté en int renvoie des valeurs entre 0 et 6 !!
Evidement c'est pas les mêmes que SQL Server, mais pas trop loin

• 1 = Lundi
• 2 = Mardi
• 3 = Mercredi
• 4 = Jeudi
• 5 = Vendredi
• 6 = Samedi
• 0 = Dimanche

Bon pour uniformiser tout cela est simplifier l'utilisation et le transfert des infos entre les deux technos je propose la formule ci dessous qui renvoie toujours les données au format c# quelque soit la configuration SQL Server.

SELECT (DATEPART(WEEKDAY, ma_date) +  @@DATEFIRST - 1) % 7 AS jour_semaine

FROM ...

Explications : Le DATEPART renvoie le jour de la semaine pour SQL Server, L'ajout du @@DateFirst -1 permet de retomber sur un nombre qui avec le Modulo (%) 7 sera toujours identique quelque soit la valeur du @@DATEFIRST et toujours compris entre 0 et 6 c'est ce qu'il faut pour c#

Enjoy !

SQL : La date du jour sans heure

Pour Obtenir en SQL la date du jour sans avoir l'heure, et afin que cela fonctionne quelque soit la culture du serveur SQL voila ce que j'ai trouvé de mieux :

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)


Pour mémoire explication :
en 1) on fait la différence en jours entre GetDate et 0 (!) ==> cela donne le nombre de jour depuis la date 0 (!)
en 2) on ajoute à la date 0 (!!) le nombre de jour trouvé en 1) ==> la date sans l'heure !!

Enjoy !

Mise à jour 06/2021 : Ou alors plus simple CONVERT(DATE, @variableDateHeure)