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 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
|
ALTER function [dbo].[CalcDateTest](@LaDate datetime2, @Transformation varchar(100))
returns datetime2
as
BEGIN
DECLARE @Resultat datetime2, @Prefixe varchar(100)=null, @Parametre varchar(10)=null
, @Operation varchar(1)=null, @quantite varchar(10)=null --numeric(19,8)
if @Transformation='DAT999'
RETURN CAST('29991231' as datetime2)
SET @Transformation=REPLACE(@Transformation, ' ', '')
-- DATSYS MMSYS DAT999
-- JFM J01 PJA DJA
SET @Resultat = @Ladate
IF CHARINDEX('(', @Transformation, 1)>0
BEGIN
SET @Prefixe = SUBSTRING(@Transformation, 1, CHARINDEX('(', @Transformation, 1)-1)
SET @Parametre = SUBSTRING(@Transformation, CHARINDEX('(', @Transformation, 1)+1, 3)
END
if @Prefixe='MMSYP'
BEGIN
SET @Prefixe='MMSYS'
SET @Ladate=DATEADD(DAY, -1, @Ladate)
END
ELSE IF @Prefixe='DATSYP'
BEGIN
SET @Prefixe='DATSYS'
SET @Ladate=DATEADD(DAY, -1, @Ladate)
END
ELSE IF @Prefixe='WWSYP'
BEGIN
SET @Prefixe='WWSYS'
SET @Ladate=DATEADD(DAY, -1, @Ladate)
END
IF CHARINDEX('+', @Transformation, 1)>0
BEGIN
SET @Operation='+'
END
ELSE IF CHARINDEX('-', @Transformation, 1)>0
BEGIN
SET @Operation='-'
END
if @Operation is not null
BEGIN
SET @Quantite= SUBSTRING( @Transformation
, CHARINDEX(@Operation, @Transformation, 1)+1
, LEN(@Transformation)-CHARINDEX(@Operation, @Transformation, 1)
)
IF @Prefixe is null
SET @Prefixe = SUBSTRING(@Transformation, 1, CHARINDEX(@Operation, @Transformation, 1)-1)
END
IF @quantite is not null
BEGIN
return '01/01/2012'
DECLARE @Qte int
SET @Qte = CAST(@quantite as int)
IF @Operation='+'
BEGIN
IF @Prefixe='MMSYS'
SET @LaDate = DATEADD(MONTH, @Qte , @LaDate)
ELSE IF @Prefixe='DATSYS'
SET @LaDate = DATEADD(DAY, @Qte , @LaDate)
ELSE IF @Prefixe = 'WWSYS'
SET @LaDate = DATEADD(DAY, @Qte *7, @LaDate)
ELSE IF @Prefixe = 'QQSYS'
SET @Ladate = DATEADD(MONTH, 3*@Qte , @LaDate)
ELSE IF @Prefixe = 'SSSYS'
SET @Ladate = DATEADD(MONTH, 6*@Qte , @LaDate)
END
ELSE IF @Operation='-'
BEGIN
IF @Prefixe='MMSYS'
SET @LaDate = DATEADD(MONTH, -1 * @Qte , @LaDate)
ELSE IF @Prefixe='DATSYS'
SET @LaDate = DATEADD(DAY, -1 * @Qte , @LaDate)
ELSE IF @Prefixe = 'WWSYS'
SET @LaDate = DATEADD(DAY, -1*@Qte *7, @LaDate)
ELSE IF @Prefixe = 'QQSYS'
SET @Ladate = DATEADD(MONTH, -1 * 3*@Qte , @LaDate)
ELSE IF @Prefixe = 'SSSYS'
SET @Ladate = DATEADD(MONTH, -1 * 6*@Qte , @LaDate)
END
END
return @Ladate
return @Ladate
END |
Partager