计算日期月份和年份在sql server中的不同列中的年龄

I have a database (it is not design by me) in which date of birth is stored in three different column as a nvarchar first column is for date second for month and third for year now how can I calculate age using these column -Thanks

我有一个数据库(它不是由我设计),其中出生日期存储在三个不同的列中,因为nvarchar第一列是第二个月的日期,第三个年份现在如何使用这些列计算年龄-Thanks

4 个解决方案

#1

This should work it out for you:

这应该适合你:

declare @t table (DOBYear nvarchar(19), DOBMonth nvarchar(7),
                  DOBDay nvarchar(13))
insert into @t(DOBYear,DOBMonth,DOBDay) values ('1978','05','17')

select (DATEPART(year,CURRENT_TIMESTAMP) - DOBYear)
    - CASE
        WHEN DATEPART(month,CURRENT_TIMESTAMP) < DOBMonth THEN 1
        WHEN DATEPART(month,CURRENT_TIMESTAMP) = DOBMonth AND
             DATEPART(day,CURRENT_TIMESTAMP) < DOBDay THEN 1
        ELSE 0 END
from @t

Basically, you just subtract the years values, and then there are a couple of cases when that will overcount by 1, so we then adjust that value if necessary.

基本上,你只需减去年份值,然后有几种情况会超过1,所以我们会在必要时调整该值。

---

We're also relying on the fact that the DATEPART function returns ints and then data-type precedence rules will then force the nvarchar values to also be converted to ints before the comparisons occur. If you want to make the conversions explicit, you can wrap each column reference with a CONVERT(int,<column>).

我们还依赖于DATEPART函数返回int的事实,然后数据类型优先规则将强制nvarchar值在比较发生之前也转换为int。如果要使转换显式化,可以使用CONVERT(int, )包装每个列引用。

#2

Try this

create function dbo.dob
(
@dob datetime
)
returns datetime
as
begin
declare @out datetime
select @out = dateadd(yy,datediff(yy,convert(date,@dob),getdate())-1,@dob)
return @out
end

For Only Years:

仅限年份:

select  convert(varchar,case    when mm > month(getdate()) 
                then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                when mm = month(getdate()) and dd>day(getdate()) 
                then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                else datediff(yy,convert(date,yy+mm+dd),getdate())end)+' Years '
 from table   

For entire dob including days:

整个dob包括天:

select  convert(varchar,case    when mm > month(getdate()) then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                when mm = month(getdate()) and dd>day(getdate()) then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                else datediff(yy,convert(date,yy+mm+dd),getdate())end)+' Years '+
        convert(varchar,case    when month(getdate()) = mm and dd>day(getdate())  then datediff(mm,dbo.dob(convert(date,yy+mm+dd)),getdate())-1%12 
                else datediff(mm,dbo.dob(convert(date,yy+mm+dd)),getdate())%12 end)+' Months And '+
        convert(varchar,datediff(dd,dbo.dob(yy+mm+dd),getdate())%case when year(getdate())%4 = 0 and mm > 2 then 366 else 365 end)+' Days '
from table

#3

Query:

  SELECT CONVERT(DATETIME,[YEAR] + '-' + [MONTH] + '-' + [Day],101) AS DOB
  FROM DateOfBirth

Output:

#4

If the person was born a month previous to the actual month, or a day before or equal to today of this month, then the age is the number of years from the year of birth to the current year. If not, is the same, less 1.

如果该人在实际月份之前一个月出生,或者在本月之前或之前一天出生,则该年龄是从出生年份到当年的年数。如果没有,则相同,少于1。

CREATE TABLE BirthDates
(YEAR INT,
MONTH INT,
DAY INT)

Test data for today (2014-09-18):

今日测试数据(2014-09-18):

INSERT INTO BirthDates VALUES(2013,9,19) -- 0 the frist birthday is still to come
INSERT INTO BirthDates VALUES(2013,9,18) -- 1 today is the first birthday
INSERT INTO BirthDates VALUES(2013,9,17) -- 1 the first birthday was yesterday
INSERT INTO BirthDates VALUES(2013,8,31) -- 1 the first birthday was last month
INSERT INTO BirthDates VALUES(2013,10,1) -- 0 the first birthday will be next month

Required query

SELECT B.*,
    CASE WHEN (MONTH(GETDATE()) > B.MONTH 
               OR (MONTH(GETDATE()) = B.MONTH AND DAY(GETDATE()) >= B.DAY))
        THEN YEAR(GETDATE()) - B.YEAR 
        ELSE YEAR(GETDATE()) - B.YEAR - 1 END AS AGE
FROM BirthDates B