我坚持我的查询。 我有一个名为表Patient 。 在该表中列有患者DOB 。 其实我想显示患者的确切年龄。
例如：
PatName DOB (MM/dd/yyyy) Age
a 06/02/1947 65 Years 1 Month/s 3 Days
b 07/10/1947 64 Years 11 Month/s 25 Days
c ----------- -----------------------
我想显示的上述格式的年龄。
我已经用Google搜索这个，但没有人帮助。
如果你有关于任何疑问这一PLS张贴。
谢谢
喜检查下面的查询。
--DROP TABLE patient
CREATE TABLE patient(PatName varchar(100),DOB date, Age varchar(100))
INSERT INTO patient
VALUES('a','06/02/1947',NULL),('b','07/10/1947',NULL),('c','12/21/1982',NULL)
;WITH CTE(PatName,DOB,years,months,days) AS
(SELECT PatName,DOB,DATEDIFF(yy,DOB,getdate()),DATEDIFF(mm,DOB,getdate()),DATEDIFF(dd,DOB,getdate()) FROM patient)
--SELECT * FROM CTE
SELECT PatName,DOB,
CAST(months/12 as varchar(5))+' Years'+CAST((months % 12) as varchar(5))+' month/s '+CAST(CASE WHEN DATEADD(MM,(months % 12),DATEADD(YY,(months/12),DOB)) <= GETDATE() then DATEDIFF(dd,DATEADD(MM,(months % 12),DATEADD(YY,(months/12),DOB)),GETDATE()) ELSE DAY(getdate()) END as varchar(5))+' days' as Age
FROM CTE
如果你遵循这个链接 ，你会发现一个函数正是这样做的：
create function dbo.F_AGE_YYYY_MM_DD
(
@START_DATE datetime,
@END_DATE datetime
)
returns varchar(10)
as
/*
Function: F_AGE_YYYY_MM_DD
This function calculates age in years, months and days
from @START_DATE through @END_DATE and
returns the age in format YYYY MM DD.
Years is the number of full years between @START_DATE and @END_DATE.
Months is the number of full months since the last full year anniversary.
Days is the number of days since the last full month anniversary.
*/
begin
declare @AGE varchar(10)
declare @AGE_IN_YEARS int
declare @AGE_IN_MONTHS int
declare @AGE_IN_DAYS int
-- Return null if @START_DATE > @END_DATE
if @START_DATE > @END_DATE begin return @AGE end
select
@AGE_IN_YEARS = AGE_IN_YEARS,
@AGE_IN_MONTHS = AGE_IN_MONTHS,
@AGE_IN_DAYS =
datediff(dd,
dateadd(mm,AGE_IN_MONTHS,
dateadd(yy,AGE_IN_YEARS,START_DATE))
,END_DATE)
from
(
select
AGE_IN_MONTHS =
case
when AnniversaryThisMonth <= END_DATE
then datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)
else datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)-1
end,
*
from
(
select
AGE_IN_YEARS =
case
when AnniversaryThisYear <= END_DATE
then datediff(yy,START_DATE,END_DATE)
else datediff(yy,START_DATE,END_DATE)-1
end,
*
from
(
select
AnniversaryThisYear =
dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
AnniversaryThisMonth =
dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE),
*
from
(
select START_DATE = dateadd(dd,datediff(dd,0,@START_DATE),0),
END_DATE = dateadd(dd,datediff(dd,0,@END_DATE),0)
) aaaa
) aaa
) aa
) a
select @AGE =
right('0000'+convert(varchar(4),@AGE_IN_YEARS),4) + ' ' +
right('00'+convert(varchar(4),@AGE_IN_MONTHS),2) + ' ' +
right('00'+convert(varchar(4),@AGE_IN_DAYS),2)
return @AGE
end
go
select [Age] = dbo.F_AGE_YYYY_MM_DD('2004-04-07','2006-02-03')
select [Age] = dbo.F_AGE_YYYY_MM_DD('2006-02-03','2006-02-03')
select [Age] = dbo.F_AGE_YYYY_MM_DD('2006-02-05','2006-02-03')
select [Age] = dbo.F_AGE_YYYY_MM_DD('1950-09-13', getdate())
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '06/02/1947'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
print cast(@years as varchar(4)) + ' years, '+ cast (@months as varchar(2))+' months, '+ cast(@days as varchar(2)) + ' days'
下面是解
DATEDIFF(year, DOB, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year, DOB, getdate()), DOB)) > getdate() THEN 1 ELSE 0 END) as Years, MONTH(getdate() - (DATEADD(year, DATEDIFF(year, DOB, getdate()), DOB))) - 1 as Month/s, DAY(getdate() - (DATEADD(year, DATEDIFF(year, DOB, getdate()), DOB))) - 1 as Days
