Ms Sql Server : Calculate Age With Accuracy Of Hours And Minuets

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 looke

Solution 1:

This query will give you date diff in minutes,

selectdatediff(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

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

Solution 2:

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

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@DiffInYearsINTDECLARE@DiffInMonthsINTDECLARE@DiffInDaysINTDECLARE@DiffInHoursINTDECLARE@DiffInMinutesINTDECLARE@DiffInSecondsINTDECLARE@TotalSecondsBIGINT-- Determine Year, Month, and Day differencesSET@DiffInYears= DATEDIFF(year, @BirthDate, @CurrentDate)
IF @DiffInYears>0SET@BirthDate= DATEADD(year, @DiffInYears, @BirthDate)
IF @BirthDate>@CurrentDateBEGIN-- Adjust for pushing @BirthDate into futureSET@DiffInYears=@DiffInYears-1SET@BirthDate= DATEADD(year, -1, @BirthDate)
ENDSET@DiffInMonths= DATEDIFF(month, @BirthDate, @CurrentDate)
IF @DiffInMonths>0SET@BirthDate= DATEADD(month, @DiffInMonths, @BirthDate)
IF @BirthDate>@CurrentDateBEGIN-- Adjust for pushing @BirthDate into futureSET@DiffInMonths=@DiffInMonths-1SET@BirthDate= DATEADD(month, -1, @BirthDate)
ENDSET@DiffInDays= DATEDIFF(day, @BirthDate, @CurrentDate)
IF @DiffInDays>0SET@BirthDate= DATEADD(day, @DiffInDays, @BirthDate)
IF @BirthDate>@CurrentDateBEGIN-- Adjust for pushing @BirthDate into futureSET@DiffInDays=@DiffInDays-1SET@BirthDate= DATEADD(day, -1, @BirthDate)
END-- Get number of seconds difference for Hour, Minute, Second differencesSET@TotalSeconds= DATEDIFF(second, @BirthDate, @CurrentDate)

-- Determine Seconds, Minutes, Hours differencesSET@DiffInSeconds=@TotalSeconds%60SET@TotalSeconds=@TotalSeconds/60SET@DiffInMinutes=@TotalSeconds%60SET@TotalSeconds=@TotalSeconds/60SET@DiffInHours=@TotalSeconds-- Display resultsSELECT@DiffInYearsAS YearsDiff,
        @DiffInMonthsAS MonthsDiff,
        @DiffInDaysAS DaysDiff,
        @DiffInHoursAS HoursDiff,
        @DiffInMinutesAS MinutesDiff,
        @DiffInSecondsAS SecondsDiff

Solution 3:

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