How to find out a person is reached 21 years old?
-
Hi expert,
If i have a birthday column, anyone can show me a simple query to tell if this person reached 21 years old or not?
Thank you.
-
Use a combination of datediff() and case functions. compare the date column with getdate()
ex.
select DATEDIFF ( year , '1980-9-3', getdate()), case when DATEDIFF ( year , '1980-9-3', getdate()) <=21 then 'FALSE' else 'TRUE' end
just replace the date sample with your column and you're done! Hope this helps.
-
Sorry not that simple. You have to take into account if their birthday has been reached or passed. This works
DECLARE @birthday AS datetime
SET @birthday = '1983-10-04'
select DATEDIFF ( year , @birthday, getdate()) - (case when getdate() < dateadd(yyyy,datediff(yyyy,@birthday,getdate()),@birthday) then 1 else 0 end)
But there may be an easier way I just don't recall. Of course wrap in a UDF to save trouble if on 2000.
-
CREATE FUNCTION fnYearsOld (@DOB datetime, @CurrentDate datetime)
RETURNS varchar(3) AS
BEGIN
DECLARE @YearsOld varchar(3)
Set @YearsOld = Year(@CurrentDate) - Year(@DOB) -
(CASE WHEN CONVERT(datetime,
CONVERT(varchar(50),YEAR(@CurrentDate))
+'-'+
CONVERT(varchar(50),MONTH(@DOB))
+'-'+
CONVERT(varchar(50),DAY(@DOB)))
> @CurrentDate THEN 1 ELSE 0 END)
Return @YearsOld
END
modify to check @YearsOld >= 21
Everett Wilson
ewilson10@yahoo.com -
If your db column name is a date-field named: birth_dt, then this should work:
SELECT birth_dt,
DATEADD('yyyy',21,birth_dt) AS birthday_21,
CASE WHEN getdate() >= DATEADD('yyyy',21,birth_dt)
THEN '21 or over'
ELSE 'Under 21'
END
Note: The DATEADD('yyyy',21,birth_dt) function adds 21 years to the birth_dt, giving the 21st birthday.
Regards,Bob Monahon
-
Bobs example has the correct test. Don't use DATEDIFF(yyyy to determine someones age! Add time difference you are testing for to the birthdate date, and compare if it is greater than or less than the current date.
Becareful using DATEDIFF, you could end up selling alcohol to under-age drinkers! This example shows you that DateDiff does not work the way you might be expecting.
DECLARE @today datetime
DECLARE @bday datetime
-- Pretend todays date is 1st Jan 2021
SET @today = CONVERT(datetime, '20210101', 112)
-- Set the bday to be 11 July 2000 (they turn 21 on 11 July 2021)
SET @bday = CONVERT(datetime, '20000711', 112)
-- How many years does DATEDIFF say have passed?
SELECT DATEDIFF (yyyy,@bday,@today) AS DATEDIFFisBad
-- But how many months have passed?
SELECT DATEDIFF (mm,@bday,@today) AS MonthsOld
-- How many actual years is that
SELECT CAST( DATEDIFF (mm,@bday,@today) as decimal(9,3)) / CAST(12 as decimal(9,3)) as ActualYears
Julian Kuiters
juliankuiters.id.au -
I agree that Bob's solution is excellent, and I'm not going to say that this is any better, just a slightly different take.
You could subtract 21 years from the current date using DATEADD as such:
select DATEADD(yyyy,-21,getdate())
Then you have a baseline for 21 year olds. Anyone whose birthday is less than or equal to the result has reached their 21st birthday. (Be sure that you do not consider the time of day). If you store the result in a local variable, you only have to do the calculation once, then just compare as many birthdays as you have.
Steve
-
Hello. I agree that using the easier script is the way to go.
With that said, it sounds like someone attacked the DATEDIFF before looking at my script
. It does give you the person's correct age. Note that the CASE statement adds one year if the current year's birthday is less than or equal to the current date.Just to doublecheck (I really don't need bad functions floating around) I tried your dates and my function returned 20.
One more note, this is actually based off an old Excel fucntion for determining age. This is my real problem as I approached this from a spreadsheet POV and not a T-SQL POV.
Everett Wilson
ewilson10@yahoo.com -
Just to add to the mix, I found some notes on determining age in one of the darker corners of my drive...
-- 2003-03-11 / Kenneth Wilhelmsson
-- Determining current age notes.
-- To be able to determine current age based on DOB date, you first must
-- decide which way to round if the birthday is today.
-- If the new year should be counted, round up (ceiling),
-- if the new year should be counted tomorrow, round down (floor)
-- current age rounded down
declare @dob char(8)
set @dob = '19620311'
select floor(datediff(day, @dob, getdate()) / 365.25)
go
-- rounded up
declare @dob char(8)
set @dob = '19620311'
select ceiling(datediff(day, @dob, getdate()) / 365.25)
go
-- sometimes DOB has no century - this query solves this.
-- rounded down
declare @dob char(6)
set @dob = '420228'
select case sign ( floor(datediff(day, @dob, getdate()) / 365.25) )
when -1
then floor(datediff(day, @dob, getdate()) / 365.25) + 100
else floor(datediff(day, @dob, getdate()) / 365.25)
end as 'age'
-- rounded up
declare @dob char(6)
set @dob = '420228'
select case sign ( ceiling(datediff(day, @dob, getdate()) / 365.25) )
when -1
then ceiling(datediff(day, @dob, getdate()) / 365.25) + 100
else ceiling(datediff(day, @dob, getdate()) / 365.25)
end as 'age'
-- some northwind examples
use northwind
go
-- 1) How old are all employees today
select Firstname + ' ' + Lastname +
' (' +
cast(floor(datediff(day, convert(char(8), BirthDate, 112), getdate()) / 365.25) as char(3)) +
' years old)' as event_name
, BirthDate
from employees
-- 2) List all with current age that have a birthday in the current month
select Firstname + ' ' + Lastname +
' (' +
cast(floor(datediff(day, convert(char(8), BirthDate, 112), getdate()) / 365.25) as char(3)) +
' years old)' as event_name
, BirthDate
from employees
where datepart(month, BirthDate) = datepart(month, getdate())
/Kenneth
-
Here are some other solutions. FWIW:
DECLARE @geburtstag DATETIME
SET @geburtstag = '19830908'
SELECT
DATEDIFF(yy, @geburtstag, GETDATE()) -
CASE
WHEN (MONTH(GETDATE()) * 100 +
DAY(GETDATE())) <
(MONTH(@geburtstag)* 100 + DAY(@geburtstag))
THEN 1 ELSE 0
END
SELECT
(CAST(CONVERT(CHAR(8),GETDATE(), 112) AS INT)
-
CAST(CONVERT(CHAR(8), @geburtstag, 112) AS INT))/10000
SELECT
YEAR(GETDATE()) - YEAR(@geburtstag) -
(CASE
WHEN (MONTH(@geburtstag) > MONTH(GETDATE()))
OR (MONTH(@geburtstag) = MONTH(GETDATE())
AND DAY(@geburtstag) > DAY(GETDATE()))
THEN 1 ELSE 0 END)
SELECT
(0+CONVERT(CHAR(8),CURRENT_TIMESTAMP, 112)
-
CONVERT(CHAR(8), @geburtstag, 112))/10000
-----------
21
(1 row(s) affected)
-----------
21
(1 row(s) affected)
-----------
21
(1 row(s) affected)
-----------
21
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply