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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
| CREATE FUNCTION [dbo].[LastDayOfMonth](@InMonth integer,@InYear integer)
RETURNS integer
AS
BEGIN
DECLARE @MyOutput Integer
IF @InMonth IN (1,3,5,7,8,10,12) BEGIN SET @MyOutput=31 END
IF @InMonth IN (4,6,9,11) BEGIN SET @MyOutput=30 END
IF @InMonth = 2 AND @InYear%4 = 0 BEGIN SET @MyOutput=29 END
IF @InMonth = 2 AND @InYear%4 <> 0 BEGIN SET @MyOutput=28 END
RETURN @MyOutput
END
GO
CREATE FUNCTION [dbo].[EstUneDate](@InDate varchar,@InHeure varchar)
RETURNS integer
AS
BEGIN
DECLARE @MyOutput Integer
DECLARE @DD Varchar(2)
DECLARE @MM Varchar(2)
DECLARE @YYYY Varchar(4)
DECLARE @HH Varchar(2)
DECLARE @MI Varchar(2)
SET @MyOutput = 0
-- Test de la longueur des chaînes en entrée
IF LEN(@InDate) != 10 BEGIN
SET @MyOutput = 1
END
ELSE BEGIN
IF LEN(@InHeure) != 5 BEGIN
SET @MyOutput = 2
END
ELSE BEGIN
-- Test des séparateurs
IF SUBSTRING(@InDate,3,1) <> '/' BEGIN
SET @MyOutput = 3
END
ELSE BEGIN
IF SUBSTRING(@InDate,6,1) <> '/' BEGIN
SET @MyOutput = 4
END
ELSE BEGIN
IF SUBSTRING(@InHeure,3,1) <> ':' BEGIN
SET @MyOutput = 5
END
ELSE BEGIN
SET @DD = SUBSTRING(@InDate,1,2)
SET @MM = SUBSTRING(@InDate,4,2)
SET @YYYY = SUBSTRING(@InDate,7,4)
SET @HH = SUBSTRING(@InHeure,1,2)
SET @MI = SUBSTRING(@InHeure,4,2)
-- Test des numériques
IF NOT(ascii(@DD) between 48 and 57) BEGIN
SET @MyOutput = 6
END
ELSE BEGIN
IF NOT(ascii(@MM) between 48 and 57) BEGIN
SET @MyOutput = 7
END
ELSE BEGIN
IF NOT(ascii(@YYYY) between 48 and 57) BEGIN
SET @MyOutput = 8
END
ELSE BEGIN
IF NOT(ascii(@HH) between 48 and 57) BEGIN
SET @MyOutput = 9
END
ELSE BEGIN
IF NOT(ascii(@MI) between 48 and 57) BEGIN
SET @MyOutput = 10
END
ELSE BEGIN
-- Test des valeurs
IF NOT(CAST(@DD as integer) between 1 and 31) BEGIN
SET @MyOutput = 11
END
ELSE BEGIN
IF NOT(CAST(@MM as integer) between 1 and 12) BEGIN
SET @MyOutput = 12
END
ELSE BEGIN
IF NOT(CAST(@YYYY as integer) between 1900 and 2099) BEGIN
SET @MyOutput = 13
END
ELSE BEGIN
IF NOT(CAST(@HH as integer) between 0 and 23) BEGIN
SET @MyOutput = 14
END
ELSE BEGIN
IF NOT(CAST(@MI as integer) between 0 and 59) BEGIN
SET @MyOutput = 15
END
ELSE BEGIN
-- Dernier Jour du Mois
IF CAST(@DD as integer) > dbo.LastDayOfMonth(CAST(@MM as integer),CAST(@YYYY as integer)) BEGIN
SET @MyOutput = 16
END
ELSE BEGIN
SET @MyOutput = 17
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
RETURN @MyOutput
END
GO |
