Le but est d'autoriser l'utilisateur à écrire un modificateur pour modifier une date. Cela permet d'être plus dynamique.
Un modificateur se compose de 3 parties :
- Type d'opération sous la forme + ou -
- Valeur à appliquer
- Partie de la date à modifier sous la forme d pour jour, w pour semaine, m pour mois et y pour année
La fonction se présente sous la forme :
CREATE FUNCTION [dbo].[ApplyDateModifier]
(
@date varchar(32),
@modifier varchar(32)
)
RETURNS varchar(32)
AS
BEGIN
declare @return varchar(32),
@type char
set @return=@date
set @type=(substring(reverse(Lower(@modifier)),1,1))
if (@type='y')
begin
set @return=dbo.FormatDate(DATEADD(YEAR, convert(int,replace(@modifier,@type,'')),@date) ,'yyyymmdd')
end
if (@type='m')
begin
set @return=dbo.FormatDate(DATEADD(MONTH, convert(int,replace(@modifier,@type,'')),@date) ,'yyyymmdd')
end
if (@type='w')
begin
set @return=dbo.FormatDate(DATEADD(WEEK, convert(int,replace(@modifier,@type,'')),@date) ,'yyyymmdd')
end
if (@type='d')
begin
set @return=dbo.FormatDate(DATEADD(DAY, convert(int,replace(@modifier,@type,'')),@date) ,'yyyymmdd')
end
return @return
END
On utilise ici la fonction système DateAdd et la fonction vue précédemment FormatDate.
Exemple d'utilisation
select dbo.ApplyDateModifier(GetDate(),'3d');
Cela affichera la date du jour + 3 jours
La limite de cette fonction est qu'elle ne peut mélanger plusieurs partie à modifier.
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')
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