CODEWITHSUNDEEP
sqlsql-server
vmd1310
vmd1310

Reputation: 21

Trying to calculate a date of birth

I have a table with 300 people in it and need to calculate their ages as of 04/01/2017 based on their DOBs. I know I am missing populating my @dob variable and can't figure it out. Here is what I have:

Declare @dob  datetime

Declare @cutoff datetime

set @cutoff = '2017-04-01'

Select dob, FLOOR((CAST (@cutoff AS INTEGER) - CAST(@dob AS INTEGER)) / 365.25) AS Age,   
 FROM  [PGADCP].[dcp].[person] p

Upvotes: 2

Views: 82

Answers (5)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

That's simple math. Subtract the birth year from the year 2017. Then check whether birth month/day is before 01/04, because in this case you must subtract a year, as that year's birthday was not reached yet.

select 
  dob, 
  2017 - year(dob) - case when '01/04' > convert(char(5), dob, 1) then 1 else 0 end as age
from pgadcp.dcp.person;

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

This should give you their exact age from the cut-off

declare @asat date = '2017-01-01'

SELECT   (DATEDIFF(HOUR, date_of_birth, @asat) / 8766) AS [Age] 
from myPeople

Test 

declare @asat date = '2017-11-17'
declare @mydbo date = '1981-11-18'

SELECT   (DATEDIFF(HOUR, @mydbo, @asat) / 8766) AS [Age]

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46233

Here's one method to calculate the age:

DECLARE @cutoff datetime = '20170401';
SELECT 
    (CAST(CONVERT(char(8), @cutoff, 112) AS int) -
    CAST(CONVERT(char(8), dob, 112) AS int)) /10000 AS Age
FROM [PGADCP].[dcp].[person];

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This is complicated in SQL Server. What you need to do is subtract three months and one day and use datediff() as of '2016-12-31'.

That is:

select p.dob,
       datediff(year, dateadd(month, -3, dob), '2016-12-31') as AgeAtCutoff

The reason is that datediff() counts year boundaries. Presumably, you don't want to add a year of age to someone born in December.

Another approach is the approximate approach:

select datediff(day, dob, '2017-04-01') / 365.25

This works pretty well for most things.

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

Rather than trying to compute the number of full years manually, use DATEDIFF function with year parameter:

SELECT
    p.dob
,   DATEDIFF(year, p.dob, @cutoff) AS AgeAsOfCutoff  
FROM  [PGADCP].[dcp].[person] p

Note that you do not need @dob variable at all, because the value of dob comes from the corresponding column in [person] table.

Upvotes: 0

Related Questions