Dates in SQL server

SQL Server is has several possibilities to calculate elapsed time (such as age). Two approaches are possible. The first idea is to calculate the number of hours and divide this by the number of hours in a year. The other possibility is to make a distinction between the number of a day in a month as compared between the first date and the last date.

Let me first declare a first date. Let us think of a date of birth.

DECLARE @dob date
SET @dob='2001-01-22'

Then the last date is declared.

DECLARE @ref date
SET @ref='2021-01-21'

Another last date is declared whereby the first day in a month is taken.

DECLARE @ref1 date
SET @ref1=convert(date,convert(char(8),DATEPART(YEAR,@ref)10000+datepart(month,@ref)100+1),112)

The first calculation provides an age.

SELECT floor(DATEDIFF(hour,@dob,@ref)/8765.82) AS AgeYears

The second calculation provides another calculation of an age.

select CASE WHEN DATEPART(DAY, @ref) < DATEPART(DAY ,@dob)
THEN DATEDIFF(YEAR, @dob, @ref) - 1
ELSE DATEDIFF(YEAR, @dob, @ref)
END as "Leeftijd"

Then the difference is calculated between the date of birth and a first day in a month.

select DATEDIFF(YEAR, @dob, @ref1) as "Leeftijd_Eerste_Maand"

Door tom