如何使用年，月和日来计算T-SQL中的年龄 [英] How to calculate age in T-SQL with years, months, and days

问题描述

在T-SQL（SQL Server 2000）中计算某人的年，月和日的年龄的最佳方法是什么？

What would be the best way to calculate someone's age in years, months, and days in T-SQL (SQL Server 2000)?

datediff 函数不能很好地处理年份的边界，而且将月份和日期分开将是一个负担。我知道我可以在客户端相对轻松地完成此操作，但是我想在我的存储的数据库中完成此操作过程。

The datediff function doesn't handle year boundaries well, plus getting the months and days separate will be a bear. I know I can do it on the client side relatively easily, but I'd like to have it done in my stored procedure.

推荐答案

下面是一些T-SQL，可为您提供自此以来的年数，月数和天数@date中指定的日期。它考虑到DATEDIFF（）计算差异而没有考虑是哪个月或一天的事实（因此8/31和9/1之间的月份差异为1个月），并使用case语句处理该差异，从而减少结果，其中

Here is some T-SQL that gives you the number of years, months, and days since the day specified in @date. It takes into account the fact that DATEDIFF() computes the difference without considering what month or day it is (so the month diff between 8/31 and 9/1 is 1 month) and handles that with a case statement that decrements the result where appropriate.

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '2/29/04'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

这篇关于如何使用年，月和日来计算T-SQL中的年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！