MS SQL Server：计算小时和小时的准确度的年龄 [英] MS SQL Server : calculate age with accuracy of hours and minuets

问题描述

我需要一个SQL函数来计算年龄。它必须准确，涵盖所有角落。

这是医院病房的婴儿，所以30岁的小时是一个常见的情况。

I need an SQL function to calculate age. It has to be accurate and cover all corner cases.
It is for hospital ward for babies, so age of 30 minuets is a common case.

我有一个其他的答案，但找不到一个处理所有情况。

I have a looked on other answers but could not find one that deals with all cases.

例如：

• 宝宝出生于2014-04-29 12： 59：00.000。


• 现在是2014-04-29 13：10：23.000，

是0年，0个月，0天，0小时， 11分钟

Age should be 0 Years, 0 Months, 0 Days, 0 Hours, 11 minutes

如果有人可以提供终极和确定的版本的功能。

It would be great if someone can provide the ultimate and definitive version of that function.

（恐怕简单的解决方案与DateDiff不够好正如更受欢迎的问题：定价函数不能很好地处理年度边界...

(I am afraid that simple solutions with DateDiff are not good enough. As stated in a more popular question : "The Datediff function doesn't handle year boundaries well ..."

推荐答案

我们可以使用 DATEDIFF 获得年，月和日差异，然后简单划分秒，分和小时的差异。

We can use DATEDIFF to get the Year, Month, and Day differences, and then simple division for the Seconds, Minutes, and Hours differences.

我已经使用 @CurrentDate 重新创建原始请求，但 @CurrentDate = GETDATE（）将在执行时返回年龄。 >

I've used @CurrentDate to recreate the original request, but @CurrentDate = GETDATE() will return the age at time of execution.

DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME
SET @BirthDate = '2014-04-29 12:59:00.000'
SET @CurrentDate = '2014-04-29 13:10:23.000'

DECLARE @DiffInYears INT
DECLARE @DiffInMonths INT
DECLARE @DiffInDays INT
DECLARE @DiffInHours INT
DECLARE @DiffInMinutes INT
DECLARE @DiffInSeconds INT
DECLARE @TotalSeconds BIGINT


-- Determine Year, Month, and Day differences
SET @DiffInYears = DATEDIFF(year, @BirthDate, @CurrentDate)
IF @DiffInYears > 0
    SET @BirthDate = DATEADD(year, @DiffInYears, @BirthDate)
IF @BirthDate > @CurrentDate
BEGIN
    -- Adjust for pushing @BirthDate into future
    SET @DiffInYears = @DiffInYears - 1
    SET @BirthDate = DATEADD(year, -1, @BirthDate)
END

SET @DiffInMonths = DATEDIFF(month, @BirthDate, @CurrentDate)
IF @DiffInMonths > 0
    SET @BirthDate = DATEADD(month, @DiffInMonths, @BirthDate)
IF @BirthDate > @CurrentDate
BEGIN
    -- Adjust for pushing @BirthDate into future
    SET @DiffInMonths = @DiffInMonths - 1
    SET @BirthDate = DATEADD(month, -1, @BirthDate)
END

SET @DiffInDays = DATEDIFF(day, @BirthDate, @CurrentDate)
IF @DiffInDays > 0
    SET @BirthDate = DATEADD(day, @DiffInDays, @BirthDate)
IF @BirthDate > @CurrentDate
BEGIN
    -- Adjust for pushing @BirthDate into future
    SET @DiffInDays = @DiffInDays - 1
    SET @BirthDate = DATEADD(day, -1, @BirthDate)
END

-- Get number of seconds difference for Hour, Minute, Second differences
SET @TotalSeconds = DATEDIFF(second, @BirthDate, @CurrentDate)

-- Determine Seconds, Minutes, Hours differences
SET @DiffInSeconds = @TotalSeconds % 60
SET @TotalSeconds = @TotalSeconds / 60

SET @DiffInMinutes = @TotalSeconds % 60
SET @TotalSeconds = @TotalSeconds / 60

SET @DiffInHours = @TotalSeconds


-- Display results
 SELECT @DiffInYears AS YearsDiff,
        @DiffInMonths AS MonthsDiff,
        @DiffInDays AS DaysDiff,
        @DiffInHours AS HoursDiff,
        @DiffInMinutes AS MinutesDiff,
        @DiffInSeconds AS SecondsDiff

这篇关于MS SQL Server：计算小时和小时的准确度的年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！