TSQL Astuce 5 : Modifier une date par un modificateur

Rédigé par marmous Aucun commentaire
Classé dans : Informatique Mots clés : developpement, TSQL, SQL Server
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.

TSQL Astuce 4 : Fonction de formatge de date

Rédigé par marmous Aucun commentaire
Classé dans : Informatique Mots clés : developpement, TSQL, SQL Server
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')

TSQL Astuce 3 : Découper une chaine de caractères délimitée par un séparateur

Rédigé par marmous Aucun commentaire
Classé dans : Informatique Mots clés : TSQL, developpement, SQL Server
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

TSQL Astuce 2 : Comment reindexer tous les indexs de toutes les tables en une ligne

Rédigé par marmous Aucun commentaire
Classé dans : Informatique Mots clés : TSQL, SQL Server, developpement
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;

TSQL Astuce 1 : Comment obtenir la liste des jours consécutifs

Rédigé par marmous Aucun commentaire
Classé dans : Informatique Mots clés : TSQL, developpement, SQL Server

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

Fil RSS des articles de ce mot clé