MS SQL Server：计算年龄，精确到小时和小步

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.

我需要一个SQL函数来计算年龄。它必须准确并涵盖所有角落案例。它适用于婴儿的医院病房,所以30分钟的年龄是常见的情况。

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

我查看了其他答案但找不到处理所有案例的答案。

For example:

Baby born in 2014-04-29 12:59:00.000.

宝宝出生于2014-04-29 12:59:00。

And now is 2014-04-29 13:10:23.000,

现在是2014-04-29 13:10:23.000,

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

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

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

如果有人能够提供该功能的最终和最终版本,那将是很棒的。

(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的简单解决方案还不够好。正如一个更受欢迎的问题所述:“Datediff函数不能很好地处理年份界限......”

3 个解决方案

#1

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

我们可以使用DATEDIFF来获取年,月和日的差异,然后使用简单的除以秒,分和小时差异。

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

我已经使用@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

#2

This query will give you date diff in minutes,

此查询将以分钟为单位为您提供日期差异,

select datediff(mi, '2014-04-23 05:23:59.660',getdate())

Then you can simply calc the minutes/60 for hours and minutes mod 60 for minutes

然后你可以简单地计算分钟/ 60小时和分钟mod 60分钟

select datediff(mi, '2014-04-23 05:23:59.660',getdate())/60 as [Hours], select datediff(mi, '2014-04-23 05:23:59.660',getdate()) % 60 as [Minutes]

#3

It will give you date diff in seconds select datediff(s, '2014-04-23 05:23:59.660',getdate())

它会给你几秒的日期差异选择datediff(s,'2014-04-23 05:23:59.660',getdate())

#1