1 /****** Object: StoredProcedure [dbo].[GetDateDiff] Script Date: 11/12/2019 16:14:05 ******/
2 SET ANSI_NULLS ON
3 GO
4 SET QUOTED_IDENTIFIER ON
5 GO
6 --計算兩個時間相差多少年月日的sql算法，用于新生兒年齡計算
7 ALTER PROCEDURE [dbo].[GetDateDiff]
8 @fromDate NVARCHAR(10) ,--開始日期
9 @toDate NVARCHAR(10) --結束日期
10 AS
11 DECLARE @YearDiff INTEGER
12 DECLARE @MonthDiff INTEGER
13 DECLARE @DayDiff INTEGER
14 DECLARE @ToMonth INTEGER
15 DECLARE @FromDay INTEGER
16 DECLARE @ToDay INTEGER
17
18
19 SET @YearDiff = LTRIM(DATEDIFF(yy, @fromDate, @toDate))
20 SET @MonthDiff = LTRIM(DATEDIFF(mm, @fromDate, @toDate) % 12)
21 SET @ToMonth = MONTH(CAST(@toDate AS DATETIME))
22 SET @FromDay = DAY(CAST(@fromDate AS DATETIME))
23 SET @ToDay = DAY(CAST(@ToDate AS DATETIME))
24 IF ( @ToDay - @FromDay ) < 0
25 BEGIN
26 IF ( @ToMonth - 1 ) = 1
27 OR ( @ToMonth - 1 ) = 3
28 OR ( @ToMonth - 1 ) = 5
29 OR ( @ToMonth - 1 ) = 7
30 OR ( @ToMonth - 1 ) = 8
31 OR ( @ToMonth - 1 ) = 10
32 OR ( @ToMonth - 1 ) = 12
33 BEGIN
34 SET @DayDiff = 31 + @Today - @FromDay
35 SET @MonthDiff = @MonthDiff - 1
36 END
37 ELSE
38 BEGIN
39 SET @DayDiff = 30 + @Today - @FromDay
40 SET @MonthDiff = @MonthDiff - 1
41 END
42 END
43 ELSE
44 BEGIN
45 SET @DayDiff = @Today - @FromDay
46 END
47
48 SELECT CAST(@YearDiff AS NVARCHAR(10)) + \'歲\'
49 + CAST(@MonthDiff AS NVARCHAR(10)) + \'個月\'
50 + CAST(@DayDiff AS NVARCHAR(10)) + \'天\'
51
