参考此帖:http://topic.csdn.net/u/20110416/13/1878ce94-6154-43a3-9b23-f5ff8c06f164.html 如果您无法打开此链接,也可以下载此论题图http://download.cnblogs.com/insus/Miscellaneous/Insus_Birthdate_Cal_Age.rar。
根据出生日期计算出在某个事件日期当时的年龄。因为Insus.NET把它写成一个自定义函数UDF_Age:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( ' [dbo].[udf_Age] ' ) IS NOT NULL
DROP FUNCTION [ dbo ] . [ udf_Age ]
GO
CREATE FUNCTION [ dbo ] . [ udf_Age ] (
@BirthDate DATETIME ,
@EventDate DATETIME
)
RETURNS INT
AS
BEGIN
-- 没有超过一岁的,均为0岁
RETURN DATEDIFF ( YEAR , @BirthDate , @EventDate ) - CASE WHEN 100 * MONTH ( @EventDate ) + DAY ( @EventDate ) < 100 * MONTH ( @BirthDate ) + DAY ( @BirthDate )
THEN 1 ELSE 0 END
END
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( ' [dbo].[udf_Age] ' ) IS NOT NULL
DROP FUNCTION [ dbo ] . [ udf_Age ]
GO
CREATE FUNCTION [ dbo ] . [ udf_Age ] (
@BirthDate DATETIME ,
@EventDate DATETIME
)
RETURNS INT
AS
BEGIN
-- 没有超过一岁的,均为0岁
RETURN DATEDIFF ( YEAR , @BirthDate , @EventDate ) - CASE WHEN 100 * MONTH ( @EventDate ) + DAY ( @EventDate ) < 100 * MONTH ( @BirthDate ) + DAY ( @BirthDate )
THEN 1 ELSE 0 END
END
演示:
SELECT
[
dbo
]
.
[
udf_Age
]
(
'
1988-08-06
'
,
'
2011-04-18
'
)
结果: