Il m'est souvent utile de devoir formater une date.
Pendant longtemps j'ai utilisé ça :
Declare @Date SmallDatetime
set @Date = GetDate()
print Right('00' + convert(varchar,DatePart(Day, @Date),2) +
'/' +
Right('00' + convert(varchar,DatePart(Month, @Date),2) +
'/' +
Convert(varchar, DatePart(Year,@Date)
Cette solution, loin d'être élégante, a de nombreux défauts.
Elle n'est pas lisible.
Elle n'accepte qu'un seul format. Si le format change je dois changer toute la formule.
Elle s'utilise en copier/coller et est donc source d'erreur.
Pour tous ces points, j'ai créé une fonction qui prend en paramètre une date et un format et qui retourne cette date formaté.
CREATE FUNCTION [dbo].[FormatDate]
(
@date smalldatetime,
@format nvarchar(30)
)
RETURNS nvarchar(30)
AS
BEGIN
declare @nbAnnee int,
@nbMois int,
@nbJour int,
@Annee varchar(4),
@Mois varchar(2),
@Jour varchar(2),
@separator varchar(1),
@index int,
@character char,
@InsertAnnee bit,
@InsertMois bit,
@InsertJour bit,
@dateOutput varchar(30)
set @nbAnnee = 0
set @nbMois = 0
set @nbJour = 0
set @separator = ''
set @index = 1
set @dateOutput = ''
set @InsertAnnee = 0
set @InsertMois = 0
set @InsertJour = 0
while (@index<=len(@format))
begin
set @character = substring(@format,@index,1)
if (@character = 'Y')
begin
set @nbAnnee=@nbAnnee+1
end
else
begin
if (@character = 'M')
begin
set @nbMois=@nbMois+1
end
else
begin
if (@character = 'D')
begin
set @nbJour=@nbJour+1
end
else
begin
set @separator = @character
end
end
end
set @index = @index +1
end
set @Annee = Right('0000' + Convert(varchar,Year(@date)),@NbAnnee)
set @Mois = Right('00' + Convert(varchar,Month(@date)),@NbMois)
set @Jour = Right('00' + Convert(varchar,Day(@date)), @NbJour)
set @index = 1
while (@index=@index)
begin
set @character = substring(@format,@index,1)
if (@character='Y')
begin
if (@InsertAnnee = 0)
begin
set @dateOutput = @dateOutput + @Annee
set @InsertAnnee = 1
end
end
else
begin
if (@character='M')
begin
if (@InsertMois = 0)
begin
set @dateOutput = @dateOutput + @Mois
set @InsertMois = 1
end
end
else
begin
if (@character='D')
begin
if (@InsertJour = 0)
begin
set @dateOutput = @dateOutput + @Jour
set @InsertJour = 1
end
end
else
begin
set @dateOutput = @dateOutput + @separator
end
end
end
if (@InsertAnnee = 1) and (@InsertMois = 1) and (@InsertJour = 1)
begin
break
end
set @index = @index +1
end
return @dateOutput
END
Cette fonction réalise son traitement en plusieurs étapes.
1ere étape : elle récupère le nombre de caractères demandé pour chaque type (Jour, Mois, Année)
2e étape : elle formate chaque type suivant la 1ere étape
3e étape : elle position chaque type et séparateur suivant le chaine de formatage passé en paramètre
Voici un exemple d'utilisation
select dbo.FormatDate(GetDate(),'dd/mm/yyyy')
A noter qu'à partir de SQL Server 2014, il existe la commande Format.
select format(GetDate(),'d','fr-fr')
Ce site et d'autres outils sont hébergés chez moi sous Ubuntu server. Pour des raisons de simplicité de maintenance, j'ai virtualisé tous mes services à l'aide de VirtualBox.
Au fil des années, l'espace que j'avais prévu devient trop juste et mon disque dur virtuel est saturé. Dans mon cas, les partitions /home et /var étaient saturées.
Voici la méthode que j'ai utilisé pour agrandir mon disque :
0x00 - Faire une sauvegarde du disque dur virtuel
0x01 - Création d'un nouveau disque
vboxmanage createhd --filename NouveauDisque.vdi --size 50000
A noter que la taille est en Mo
0x02 - Sauvegarde des données
sudo cp -a /home /homebak
sudo cp -a /var /var
L'option a de la commande cp permet entre autre de garder les droits sur les fichiers.
0x03 - Identifier les partitions
df -h
Notes bien les informations. ce sera utile à l'étape 8.
0x04 - Arrêt de la machine virtuelle
0x05 - Clone de l'ancien disque sur le nouveau
vboxmanage clonehd AncienDisque.vdi NouveauDisque.vdi --existing
0x06 - Attacher le nouveau disque
Tu détaches l'ancien disque de la machine virtuelle
vboxmanage modifyvm NomMachineVirtuelle --hda none
Tu supprimes les deux disques du gestionnaire de média VirtualBox
vboxmanage closemedium disk AncienDisque.vdi
vboxmanage closemedium disk NouveauDisque.vdi
Tu les renommes pour faire plus propre
mv AncienDisque.vdi AncienDisque_Copie.vdi
mv NouveauDisque.vdi AncienDisque.vdi
Tu attaches le nouveau disque renommé sur la machine virtuelle
vboxmanage modifyvm NomMachineVirtuelle --hda AncienDisque.vdi
0x07 - Démarrage en mode récupération
Une fois que tu es en mode récupération, ouvres une console root.
0x08 - Suppression et création des partition à la nouvelle taille
fdisk /dev/sda
p pour afficher la table des partitions
d pour supprimer une partition
n pour créer une partition
Si tu as noté les informations de l'étape 3, tu sais quelles partitions sont concernées.
0x09 - Redémarrage
0x0a - Création des systèmes de fichiers
mkfs.ext4 /dev/sda3
mkfs.ext4 /dev/sda4
0x0b - Modification du fichier fstab
sudo nano /etc/fstab
Remplace les UUID par /dev/sdaX
0x0c - Montage des partition
mount /home
mount /var
0x0d - Restauration des données
cp -a /varbak/* /var
cp -a /homebak/* /home
0x0e - Redémarrage
0x0f - Test
0x10 - Suppression des sauvegardes
rm -rf /homebak
rm -rf /varbak
0x11 - Suppression de l'ancien disque
rm -f AncienDisque_Copie.vdi
Voici une fonction qui renvoie une table où chaque élément est un morceau de la chaine initiale :
CREATE FUNCTION [dbo].[SplitString](
@String NVARCHAR(4000),
@Delimiter NCHAR(1))
RETURNS TABLE
AS
RETURN (
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
Cette fonction utilise plusieurs mécanisme de SQL Server.
Elle repose sur l'utilisation d'une CTE (http://msdn.microsoft.com/fr-fr/library/ms175972.aspx) qui retourne une table contenant la position de chaque "mot" en fonction du délimiteur passé en paramètre.
Cette table est ensuite utilisée pour construire la table qui sera retournée.
La fonction SUBSTRING découpe chaque "mot".
Notez l'utilisation des mots-clé COALESCE et NULLIF pour éviter les effets de bords.
Utilisation
select * from dbo.SplitString('pomme,banane,myrtille,fraise,cerise,melon,pastèque',',');
Cette requête renvoie :
1 pomme
2 banane
3 myrtille
4 fraise
5 cerise
6 melon
7 pastèque
Tout est dans le titre ;-)
Pour reconstruire tous les indexes d'une base
exec sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD;"
Pour réorganiser tous les indexes d'une base
exec sp_MSforeachtable "ALTER INDEX ALL ON ? REORGANIZE;"
Attention cette opération est extrêmement couteuse en ressource. En production il est préférable de ne réaliser ce type d'opération que sur des indexes fragmentés. Vous trouverez un exemple de script ici
Bonus :
Pour être sûr que les statistiques de la base sont à jour, vous pouvez lancer ensuite la commande
exec sp_UpdateStats;
Aujourd'hui, une astuce permettant d'extraire des plages de dates contigües d'un planning.
Dans le cas d'un planning, nous avons une table Planning comme suit :
Create table @Planning (UserId uniqueidentifier, Activities nvarchar(255), Date smalldatetime)
Elle contient ceci :
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140801'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140802'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140803'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140804'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140805'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140806'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140807'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140808'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140809'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140810'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Repos', '20140811'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Repos', '20140812'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Repos', '20140813'
Il faut d'abord faire une première requête en tagant les enregistrements étant consécutif.
Pour détecter les enregistrements consécutifs on utilise ici la commande ROW_NUMBER() OVER (http://msdn.microsoft.com/fr-fr/library/ms186734.aspx) quelque peu détournée de sa fonction première. On obtient donc la même valeur DtRange pour des date qui se suivent.
Nous utilisons ensuite cette première requête en sous requête pour regrouper les valeurs par intervalle de date.
Voici la requête finale :
select ip2.UserId,
ip2.Activities,
Min(ip2.Date) as deb,
Max(ip2.Date) as fin
from(select UserId,
Activities,
Date,
DateDiff(Day, Row_Number() over(order by Date), Date) AS DtRange
from @Planning
) as ip2
Group by ip2.DtRange, ip2.UserId, ip2.Activities