MS SQL Server：以小时和分钟的精度计算年龄

小编典典

MS SQL Server：以小时和分钟的精度计算年龄

sql

我需要一个SQL函数来计算年龄。它必须准确并涵盖所有极端情况。
它是婴儿医院的病房，所以30分钟的年龄很常见。

我看了其他答案，但找不到能解决所有情况的答案。

例如：

婴儿出生于2014-04-29 12：59：00.000。
现在是2014-04-29 13：10：23.000

年龄应为0年，0个月，0天，0小时， 11分钟

如果有人可以提供该功能的最终版本，那就太好了。

阅读 215

2021-04-14

共1个答案

小编典典

我们可以DATEDIFF用来获取年，月和日的差异，然后对秒，分钟和小时的差异进行简单划分。

我曾经@CurrentDate重新创建原始请求，但是@CurrentDate = GETDATE()会在执行时返回年龄。

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

2021-04-14