我需要显示客户的年龄而不是 dob

Question

我需要显示客户的年龄。在查询中是否有一种不来自存储过程的方法，可以返回客户端的年龄?

数据如下:(ac.dob)

2004-04-13 00:00:00.000

查询:

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

Answer 1

您可以使用

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

Answer 2

您可以使用这个查询获得详细的年龄:

--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.'
)

输出:

18 year, 3 month, 5 day.