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 !

SQL : Pagination

 Ca arrive, on a besoin de paginer le résultat d'une requête.

Je viens d'apprendre en relisant la documentation de l'instruction ORDER BY, qu'on pouvait faire cela tout simplement grâce à cette instruction ! (SQL Server 2012 et plus uniquement).

SELECT *
FROM dbo.demo
ORDER BY dem_date OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY

Bon c'est verbeux !

OFFSET x ROWS : saute les x premières lignes 

FETCH NEXT y ROWS ONLY : renvoie uniquement que y lignes

Ca a du bon de lire la doc parfois.

Enjoy !

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16

SQL et XML

J'ai du XML, comment en extraire les données, avec SQL Server bien sûr ??

Exemple de XML : 

<Instances>
    <Instance Caption="nom1" Value="234" />
    <Instance Caption="nom2" Value="42" />
    <Instance Caption="nom3" Value="37" />
</Instances>

Un simple CONVERT en XML, fonctionne si le xml est bien formé. Mais pour en extraire les données je propose : 

WITH N AS (
    SELECT CONVERT(XML, '<Instances>
    <Instance Caption="nom1" Value="234" />
    <Instance Caption="nom2" Value="42" />
    <Instance Caption="nom3" Value="37" />
</Instances>') AS [xml]
)
SELECT m.c.value('@Caption', 'VARCHAR(MAX)') AS Caption
             ,  m.c.value('@Value', 'INT') AS Value
FROM N 
OUTER APPLY N.xml.nodes('Instances/Instance') AS m(c)
;

Ouai pas simple !  j'essaie d'expliquer…

• WITH pour créer une table N avec une colonne [xml]
• N.[xml].nodes (Attention nodes en minuscule obligatoire !!) pour éclater les données xml en données relationnelles (Voir documentation) 
• N.xml.nodes('...') AS m(c) pour créer une table m avec une colonne c. 
• N.xml.nodes('Instances/Instance')  A noter ici "le chemin xml" passé 'Instances/Instance' pour savoir sur quoi itérer.
• OUTTER APPLY pour calculer les lignes à partir du xml (Voir une bonne explication) 
• m.c.value(XQuery, SQLType) pour extraire les valeurs des attributs  (Attention value en minuscule obligatoire !!) (Voir Documentation). A noter l'attribut s'extrait avec un @ devant.

Ce pattern peut s'appliquer de manière systématique. Il peut rendre des services.

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 - Niveau de compatibilité

 Ajuster le niveau de compatibilité d'une base peut être utile pour disposer :

• de stabilité (quand on baisse le niveau) 
• ou des dernières nouveautés (quand on monte le niveau).

Dernièrement, j'ai restauré une base sur un serveur SQL dernière génération et je me suis aperçu que la compatibilité de la base restaurée ne me permettait pas d'utiliser certaines "nouvelles fonctions" (Comme STRING_SPLIT par exemple) !

Ajuster la compatibilité, n'est pas trop sorcier, le plus complexe est de s'assurer que tout fonctionne dans l'applicatif associé (La je peux rien pour vous aider), mais aussi trouver la bonne valeur du niveau ajustable en fonction de la version de son serveur SQL.

Ok SQL Server est super bien documenté, en cherchant un peu on arrive : 

1. A savoir sur quelle version du serveur SQL on est
2. A trouver le niveau de compatibilité associé
3. A exécuter la bonne requête.

Oui, oui on y arrive aisément. Ou alors…

Mais moi j'ai écris cette requête que je peux utiliser les yeux fermés et toujours de la même manière après chaque restauration de base :

DECLARE @ModelLevel INT, @CurrentLevel INT;

SELECT @ModelLevel   = [compatibility_level] FROM sys.databases WHERE name = 'model';

SELECT @CurrentLevel = [compatibility_level] FROM sys.databases WHERE name = DB_NAME();

IF (@CurrentLevel < @ModelLevel)

BEGIN

    DECLARE @sql VARCHAR(MAX);

    SET @sql  = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = ' + FORMAT(@ModelLevel, '0') + ';';

    EXEC (@sql);

END 

En gros,  on utilise le niveau de compatibilité de la base "Model" pour ajuster le niveau de la base en cours. La base "Model" étant toujours au meilleur niveau associé à notre serveur SQL (si on y a pas déjà touché !!), en faisant cela on est sûr de prendre le bon niveau.

Bon c'est peut être pas super utile, mais à moi cela me sert assez souvent !

Enjoy ?

SQL Server : Recupérer l'auto incrémenté (1 de plus !)

 Pour récupérer la valeur d'une colonne auto incrémenté dans SQL Server on dispose de 3 méthodes : 

• SCOPE_IDENTITY()
• @@IDENTITY
• IDENT_CURRENT('Table')

Pour trouver quoi utiliser et quand, je vous laisse voir les documentation associées.

Pour ma part, j'ai pris l'habitude d'utiliser SCOPE_IDENTITY(), mais récemment, je me suis retrouvé bloqué par un INSERT dans une vue avec un trigger INSTEAD OF qui fait que le contexte d'insertion est différent du context d'appel et donc qui fait que  SCOPE_IDENTITY() renvoie NULL ;-(

Du coup pour ne plus se faire avoir (nouvelle grande décision), maintenant j'utilise l'instruction ci dessous plus longue mais marche dans plus de cas :

DECLARE @id INT;
SELECT @id = COALESCE(SCOPE_IDENTITY(), @@Identity);

Bien sûr il faut remettre cette astuce dans le seul contexte ou l'on ne veut insérer qu'une seule ligne à la fois.
Il faut utiliser une clause OUTPUT si plusieurs lignes vont être insérées en une seule opération.

Enjoy !

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 !