I need to display the client's age not the dob-CodePudding

I need to display the client age. Is there a way within the query not from a stored procedure, that will return the client's age?

The data looks like this: (ac.dob)

2004-04-13 00:00:00.000

Query:

select 
    ac.first_name, ac.last_name, ac.dob, 
    st.description, co.description, 
    rd.race, rd.ethnicity_description  
from 
    address ad
inner join 
    all_clients_view ac on ad.people_id = ac.people_id
inner join 
    county co on ad.county = co.county_id
inner join 
    state st on co.state_id = st.state_id
inner join 
    rpt_demographics rd on ac.people_id = rd.people_id
where 
    ad.is_active = 1
order by 
    st.description, co.description

CodePudding user response：

You can use

DATEDIFF(yy, as.dob, getdate()) as Age

CodePudding user response：

you can get detailed age using this query:

--replace value of dob with ac.dob
declare @dob datetime = '2004-04-13 00:00:00.000' --ac.dob
declare @currentDate datetime = getdate()
DECLARE @getmm INT
DECLARE @getdd INT

declare @yy int = 0
declare @mm int = 0
declare @dd int = 0


SET @yy = DATEDIFF(yy, @dob, @currentDate)
SET @mm = DATEDIFF(mm, @dob, @currentDate)
SET @dd = DATEDIFF(dd, @dob, @currentDate)
SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dob), @currentDate))
SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dob), @currentDate), DATEADD(yy, @yy, @dob)), @currentDate))

select (
  Convert(varchar(10),@yy)   ' year, '   Convert(varchar(10),@getmm)   ' month, '    Convert(varchar(10),@getdd)   ' day.'
)

output:

18 year, 3 month, 5 day.