iT邦幫忙

DAY 7
12

T-SQL 應用之美系列 第 7

如何使用 T-SQL 計算年齡

在看過先前那麼多篇關於日期計算的討論之後,這次我們要討論最簡單的日期計算,那就是如何計算出年齡。
這個問題看似很簡單,但是相信看過這篇討論之後,您就會覺得事實絕非如我們所想見的這麼單純!
如果資料表中,有個出生日期的欄位,一般都不會再特別加上一個「年齡」的欄位,原因是因為透過 T-SQL 就可以算出來,所以不需要額外佔用一個欄位:

-- 自訂一個 DATETIME 型別的變數
DECLARE @myDate as DATETIME

-- 指定日期變數
SET @myDate = '19880907'

-- 以「年」為單位計算出年齡
SELECT DATEDIFF(yy, @myDate, getdate()) as 年齡

執行結果如下:

上面的程式邏輯看似沒問題,但是,假設有人在前一年的年底出生,請問當我們快樂地跨完新的一年之後,這個人的年齡應該是多少?
1 歲?還是 0 歲?
就中國人的虛歲來看,是 1 歲沒錯,但是如果牽扯到錢的計算,那可就不能馬虎地胡亂計算一通了。因為還沒到新的一年的年底,所以還是要當成 0 歲來計算。
透過下面這段程式碼,我們可以看出上面這樣的計算方式是錯誤的!

-- 宣告變數
DECLARE @myDate DATETIME, @today DATETIME

-- 指定日期變數
SET @myDate = '20081231'
SET @today = '20090101'

-- 以「年」為單位計算出年齡
SELECT DATEDIFF(yy, @myDate, @today) 年齡

執行結果如下:

之所以錯誤是因為 DATEDIFF() 計算出來的是這兩個日期之間,差了多少年,而非實際上,這兩個日期之間的年數(也就是年齡),所以要改寫成下面這樣:

-- 宣告變數
DECLARE @myDate DATETIME, @age int, @day DATETIME

-- 指定日期變數
SET @myDate = '19880907'

-- 以「年」為單位計算出年齡
SET @age = DATEDIFF(yy, @myDate, getdate()) -
		CASE WHEN @day < DATEADD(yy, DATEDIFF(yy, @myDate, @day), @myDate)
			THEN 1
			ELSE 0
		END

SELECT @age 年齡

上一篇
如何使用 T-SQL 找出特定日期該月的第一個星期一之日期
下一篇
如何找出某個日期區間內的資料
系列文
T-SQL 應用之美30

2 則留言

0
ziagopan
iT邦新手 5 級 ‧ 2008-10-08 15:39:21

有一個簡便的方法分享一下,建一個 SQL compute 欄位,公式如下:
(datediff(year,[日期格式的生日欄位],getdate()))

接下來除了讀取這個欄位之外,還可以進行排序、分群等應用。

alexc iT邦高手 1 級 ‧ 2008-10-08 16:14:50 檢舉

謝謝分享

這篇分享的重點在於:
DATEDIFF() 計算出來的是這兩個日期之間,差了多少年,而非實際上,這兩個日期之間的年數(也就是年齡)

ziagopan iT邦新手 5 級 ‧ 2008-10-09 16:20:09 檢舉

慚愧慚愧...沒看清楚...
而且一直以來誤會大了...
感謝您的分享與指正...

0
tzungshiun
iT邦新手 5 級 ‧ 2013-07-31 16:49:09

你的算法乍看之下似乎是對的,
但實際上也是錯誤的,

實際假設一個與當日相同的生日下去算就會發現,
EX:
今日為20130731
以兩個生日做驗證「20120505」與「20120910」
很明顯可以看出帶入之後 20120505 應為0歲, 20120910 應為1歲,
但結果卻不然,兩者運行之後結果皆為 1,
由此可知您並未做資料驗算,錯得相當離譜。

我要留言

立即登入留言