1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
| CREATE PROCEDURE psGetDataBetweenDates
@mois TINYINT,
@annee SMALLINT
AS
BEGIN
SET NOCOUNT ON
------------------------------
-- Vérification des paramètres
------------------------------
IF @mois IS NULL OR @annee IS NULL
BEGIN
RAISERROR('Les paramètres doivent être correctement valués', 16, 1)
RETURN
END
IF @mois NOT BETWEEN 1 AND 12
BEGIN
RAISERROR('Mois incorrect !', 16, 1)
RETURN
END
IF @annee NOT BETWEEN 0 AND YEAR(GETDATE())
BEGIN
RAISERROR('Année incorrecte !', 16, 1)
RETURN
END
-------------
-- Traitement
-------------
DECLARE @dateDebut DATETIME,
@dateFin DATETIME,
@moisChaine CHAR(2)
SELECT @moisChaine = CASE LEN(@mois)
WHEN 1 THEN '0' + CAST(@mois AS CHAR(1))
ELSE CAST(@mois AS CHAR(2))
END
SELECT @dateDebut = CAST(CAST(@annee AS CHAR(4)) + @moisChaine + '01' AS DATETIME) -- premier jour du mois
SELECT @dateFin = DATEADD(day, -1, DATEADD(month, 1, @dateDebut)) -- dernier jour du mois
SELECT mesColonnes
FROM dbo.maTable
WHERE maColonneDate BETWEEN @dateDebut AND @dateFin |
Partager