Inline Query

Question

Post reply

Inline Query

todd.ayers

SSCommitted

Points: 1837
More actions
October 24, 2013 at 12:01 pm

#300453

How could I write a sql statement to where I could get an age in one field based off typing a date in another field?
I am trying to get age(years, months, weeks) field to populate once I type in a date in another field. Is that possible?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bill Talada SSChampion Points: 11954 More actions · Answer 1

declare @t table ( birthdate date, age as datediff(year,birthdate,getdate()) ) ;

insert into @t (birthdate) values ('20000101')

select * from @t

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Bill Talada (10/24/2013)
declare @t table ( birthdate date, age as datediff(year,birthdate,getdate()) ) ;
insert into @t (birthdate) values ('20000101')
select * from @t

I think this will do the trick to get someone's age in years:

declare @t table ( birthdate date, age as datediff(year,birthdate,getdate())-

case when getdate()<dateadd(yy,datediff(yy,birthdate,getdate()), birthdate) then 1 else 0 end );

insert into @t (birthdate) values ('20000101')

select * from @t

Lynn Pettis published a great article on this topic: Calculating Age[/url]

Edit: Typo

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

todd.ayers (10/24/2013)
How could I write a sql statement to where I could get an age in one field based off typing a date in another field?
I am trying to get age(years, months, weeks) field to populate once I type in a date in another field. Is that possible?

To get age in years, month, weeks you could do this:

declare @t table

(birthdate date,

age_yrs as datediff(year,birthdate,getdate())-

case

when getdate()<dateadd(yy,datediff(yy,birthdate,getdate()), birthdate)

then 1

else 0

end,

age_mos as (datediff(mm,birthdate,getdate())-

case

when getdate()<dateadd(mm,datediff(mm,birthdate,getdate()), birthdate)

then 1

else 0

end)%12,

age_wks as (datediff(week,birthdate,getdate())-

case

when getdate()<dateadd(week,datediff(week,birthdate,getdate()), birthdate)

then 1

else 0

end)%52);

insert into @t (birthdate) values ('20121015')

select * from @t

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

It is Never a good idea to store the age in a column. Because you would have to perform an update everyday to update anyone with a birthday today. It is much better to store only the Birthdate and calculate all and any other values needs when you query that record.

Andrew SQLDBA

manickavasagam.g SSC Journeyman Points: 75 More actions · Answer 5

declare @BDate datetime = '01 jan 1990'

select DATEDIFF(yy,@BDate, getdate()) as AgeInYears, DATEDIFF(mm,@BDate, getdate()) as AgeInMonths, DATEDIFF(WW,@BDate, getdate()) as AgeInWeeks

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

manickavasagam.g (10/25/2013)
declare @BDate datetime = '01 jan 1990'
select DATEDIFF(yy,@BDate, getdate()) as AgeInYears, DATEDIFF(mm,@BDate, getdate()) as AgeInMonths, DATEDIFF(WW,@BDate, getdate()) as AgeInWeeks

Take a look at what happens when you change @BDate to '01 nov 1990' 😉

Try this:

selectDATEDIFF(yy,@BDate, getdate())-

case when getdate()<dateadd(yy,datediff(yy,@BDate,getdate()), @BDate) then 1 else 0 end as AgeInYears

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001