blocks|key|1215542|text|一定要把这个扔出去。如果您使用112样式(yyyymmdd)将日期convert为一个数字，则可以使用如下计算方式...|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1215543|(yyyyMMdd+-+yyyyMMdd)+/+10000+=全年差值|1215544|declare+@as_of+datetime,+@bday+datetime;
select+@as_of+=+'2009/10/15',+@bday+=+'1980/4/20'

select+
++++Convert(Char(8),@as_of,112),
++++Convert(Char(8),@bday,112),
++++0+%2B+Convert(Char(8),@as_of,112)+-+Convert(Char(8),@bday,112),+
++++(0+%2B+Convert(Char(8),@as_of,112)+-+Convert(Char(8),@bday,112))+/+10000|code-block|syntax|javascript|1215545|输出|1215546|20091015++++19800420++++290595++29|1215547|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms187928.aspx^0|X|7|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|13|8|@]|9|@]|C|$I|J]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Gotta throw this one out there. If you <a href="https://msdn.microsoft.com/en-us/library/ms187928.aspx" rel="noreferrer">convert</a> the date using the 112 style (yyyymmdd) to a number you can use a calculation like this...

(yyyyMMdd - yyyyMMdd) / 10000 = difference in full years

<pre><code>declare @as_of datetime, @bday datetime;
select @as_of = '2009/10/15', @bday = '1980/4/20'

select 
 Convert(Char(8),@as_of,112),
 Convert(Char(8),@bday,112),
 0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112), 
 (0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112)) / 10000
</code></pre>

output

<pre><code>20091015 19800420 290595 29
</code></pre>

blocks|key|1430347|text|我在我们的生产代码中使用这个查询已经将近10年了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430348|SELECT+FLOOR((CAST+(GetDate()+AS+INTEGER)+-+CAST(Date_of_birth+AS+INTEGER))+/+365.25)+AS+Age|code-block|syntax|javascript|1430349|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I have used this query in our production code for nearly 10 years:

<pre><code>SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age
</code></pre>

blocks|key|1430310|text|您需要考虑datediff命令的循环方式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430311|SELECT+CASE+WHEN+dateadd(year,+datediff+(year,+DOB,+getdate()),+DOB)+>+getdate()
++++++++++++THEN+datediff(year,+DOB,+getdate())+-+1
++++++++++++ELSE+datediff(year,+DOB,+getdate())
+++++++END+as+Age
FROM+<table>|code-block|syntax|javascript|1430312|我是从here改编的。|offset|length|1430313|请注意，对于非闰年，它将把2月28日视为小学生的生日，例如，2020年2月29日出生的人将在2021年2月28日被视为1岁，而不是2021年3月1日。|1430314|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlservercentral.com/Forums/FindPost796805.aspx^0|0|0|3|4|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

You need to consider the way the datediff command rounds.

<pre><code>SELECT CASE WHEN dateadd(year, datediff (year, DOB, getdate()), DOB) &gt; getdate()
 THEN datediff(year, DOB, getdate()) - 1
 ELSE datediff(year, DOB, getdate())
 END as Age
FROM &lt;table&gt;
</code></pre>

Which I adapted from <a href="http://www.sqlservercentral.com/Forums/FindPost796805.aspx" rel="noreferrer">here</a>.

Note that it will consider 28th February as the birthday of a leapling for non-leap years e.g. a person born on 29 Feb 2020 will be considered 1 year old on 28 Feb 2021 instead of 01 Mar 2021.

blocks|key|1430573|text|我相信这与这里发布的其他文章类似……但是这个解决方案适用于闰年的例子，从1976年的02/29到2011的01/03，在第一年也是如此。像07/04/2011到07/03/2012，上一篇关于闰年解决方案的帖子在第一年的用例中不起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430574|SELECT+FLOOR(DATEDIFF(DAY,+@date1+,+@date2)+/+365.25)|code-block|syntax|javascript|1430575|找到here。|offset|length|1430576|entityMap|0|LINK|mutability|MUTABLE|url|http://blogs.x2line.com/al/archive/2007/08/10/3235.aspx^0|0|0|2|4|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

I believe this is similar to other ones posted here.... but this solution worked for the leap year examples 02/29/1976 to 03/01/2011 and also worked for the case for the first year.. like 07/04/2011 to 07/03/2012 which the last one posted about leap year solution did not work for that first year use case.

<pre><code>SELECT FLOOR(DATEDIFF(DAY, @date1 , @date2) / 365.25)
</code></pre>

Found <a href="http://blogs.x2line.com/al/archive/2007/08/10/3235.aspx" rel="nofollow">here</a>.

blocks|key|1216276|text|由于没有一个简单的答案总是给出正确的年龄，下面是我想出来的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1216277|SELECT+DATEDIFF(YY,+DateOfBirth,+GETDATE())+-+
+++++CASE+WHEN+RIGHT(CONVERT(VARCHAR(6),+GETDATE(),+12),+4)+>=+
+++++++++++++++RIGHT(CONVERT(VARCHAR(6),+DateOfBirth,+12),+4)+
+++++THEN+0+ELSE+1+END+AS+AGE+|code-block|syntax|javascript|1216278|这将获取出生日期和当前日期之间的年份差。如果生日还没有过去，它会减去一年。|1216279|无论是闰年还是离生日有多近的时间，都是准确的。|1216280|最棒的是--没有函数。|1216281|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Since there isn't one simple answer that always gives the correct age, here's what I came up with. 

<pre><code>SELECT DATEDIFF(YY, DateOfBirth, GETDATE()) - 
 CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) &gt;= 
 RIGHT(CONVERT(VARCHAR(6), DateOfBirth, 12), 4) 
 THEN 0 ELSE 1 END AS AGE 
</code></pre>

This gets the year difference between the birth date and the current date. Then it subtracts a year if the birthdate hasn't passed yet.

Accurate all the time - regardless of leap years or how close to the birthdate. 

Best of all - no function.

blocks|key|1215617|text|下面是什么：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1215618|DECLARE+@DOB+datetime
SET+@DOB='19851125'+++
SELECT+Datepart(yy,convert(date,GETDATE())-@DOB)-1900|code-block|syntax|javascript|1215619|这不是可以避免所有的舍入、截断和设置问题吗？|1215620|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

What about:

<pre><code>DECLARE @DOB datetime
SET @DOB='19851125' 
SELECT Datepart(yy,convert(date,GETDATE())-@DOB)-1900
</code></pre>

Wouldn't that avoid all those rounding, truncating and ofsetting issues?

blocks|key|1430402|text|DECLARE+@DOB+datetime
set+@DOB+='11/25/1985'

select+floor(
(+cast(convert(varchar(8),getdate(),112)+as+int)-
cast(convert(varchar(8),@DOB,112)+as+int)+)+/+10000
)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1430403|来源：http://beginsql.wordpress.com/2012/04/26/how-to-calculate-age-in-sql-server/|unstyled|offset|length|1430404|entityMap|0|LINK|mutability|MUTABLE|url|http://beginsql.wordpress.com/2012/04/26/how-to-calculate-age-in-sql-server/^0|0|3|24|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>DECLARE @DOB datetime
set @DOB ='11/25/1985'

select floor(
( cast(convert(varchar(8),getdate(),112) as int)-
cast(convert(varchar(8),@DOB,112) as int) ) / 10000
)
</code></pre>

source: <a href="http://beginsql.wordpress.com/2012/04/26/how-to-calculate-age-in-sql-server/" rel="nofollow">http://beginsql.wordpress.com/2012/04/26/how-to-calculate-age-in-sql-server/</a>

blocks|key|1215622|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1215623|DECLARE+@date+datetime,+@tmpdate+datetime,+@years+int,+@months+int,+@days+int
SELECT+@date+=+'08/16/84'

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())

SELECT+Convert(Varchar(Max),@years)%2B'+Years+'%2B+Convert(Varchar(max),@months)+%2B+'+Months+'%2BConvert(Varchar(Max),+@days)%2B'days'|code-block|syntax|javascript|1215624|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try This 

<pre><code>DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '08/16/84'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) &gt; MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) &gt; DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) &gt; DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT Convert(Varchar(Max),@years)+' Years '+ Convert(Varchar(max),@months) + ' Months '+Convert(Varchar(Max), @days)+'days'
</code></pre>

blocks|key|1431252|text|在尝试了许多方法之后，使用现代的MS+SQL格式化函数而不是转换为样式112，这在100%25的时间内都有效。这两个都可以工作，但这是最少的代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1431253|有人能找到一个不起作用的日期组合吗？我不认为有一个:)|1431254|--Set+parameters,+or+choose+from+table.column+instead:

DECLARE+@DOB++++DATE+=+'2000/02/29'+--+If+@DOB+is+a+leap+day...
+++++++,@ToDate+DATE+=+'2018/03/01'+--...there+birthday+in+this+calculation+will+be+

--0%2B+part+tells+SQL+to+calc+the+char(8)+as+numbers:
SELECT+[Age]+=+(0%2B+FORMAT(@ToDate,'yyyyMMdd')+-+FORMAT(@DOB,'yyyyMMdd')+)+/10000|code-block|syntax|javascript|1431255|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

After trying MANY methods, this works 100% of the time using the modern MS SQL FORMAT function instead of convert to style 112. Either would work but this is the least code.

Can anyone find a date combination which does not work? I don't think there is one :)

<pre><code>--Set parameters, or choose from table.column instead:

DECLARE @DOB DATE = '2000/02/29' -- If @DOB is a leap day...
 ,@ToDate DATE = '2018/03/01' --...there birthday in this calculation will be 

--0+ part tells SQL to calc the char(8) as numbers:
SELECT [Age] = (0+ FORMAT(@ToDate,'yyyyMMdd') - FORMAT(@DOB,'yyyyMMdd') ) /10000
</code></pre>

blocks|key|1430282|text|SELECT+ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME,+DOB),GETDATE())+AS+AGE,
DOB
FROM+MyTable|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1430283|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME, DOB),GETDATE()) AS AGE,
DOB
FROM MyTable
</code></pre>

blocks|key|1216310|text|CASE+WHEN+datepart(MM,+getdate())+<+datepart(MM,+BIRTHDATE)+THEN+((datepart(YYYY,+getdate())+-+datepart(YYYY,+BIRTH_DATE))+-1+)
+++++ELSE+
++++++++CASE+WHEN+datepart(MM,+getdate())+=+datepart(MM,+BIRTHDATE)
++++++++++++THEN+
++++++++++++++++CASE+WHEN+datepart(DD,+getdate())+<+datepart(DD,+BIRTHDATE)+THEN+((datepart(YYYY,+getdate())+-+datepart(YYYY,+BIRTHDATE))+-1+)
++++++++++++++++++++ELSE+(datepart(YYYY,+getdate())+-+datepart(YYYY,+BIRTHDATE))
++++++++++++++++END
++++++++ELSE+(datepart(YYYY,+getdate())+-+datepart(YYYY,+BIRTHDATE))+END++++++++++++
++++END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1216311|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CASE WHEN datepart(MM, getdate()) &lt; datepart(MM, BIRTHDATE) THEN ((datepart(YYYY, getdate()) - datepart(YYYY, BIRTH_DATE)) -1 )
 ELSE 
 CASE WHEN datepart(MM, getdate()) = datepart(MM, BIRTHDATE)
 THEN 
 CASE WHEN datepart(DD, getdate()) &lt; datepart(DD, BIRTHDATE) THEN ((datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE)) -1 )
 ELSE (datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE))
 END
 ELSE (datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE)) END 
 END
</code></pre>

blocks|key|1430398|text|这样如何：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430399|SET+@Age+=+CAST(DATEDIFF(Year,+@DOB,+@Stamp)+as+int)
IF+(CAST(DATEDIFF(DAY,+DATEADD(Year,+@Age,+@DOB),+@Stamp)+as+int)+<+0)+
++++SET+@Age+=+@Age+-+1|code-block|syntax|javascript|1430400|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

How about this:

<pre><code>SET @Age = CAST(DATEDIFF(Year, @DOB, @Stamp) as int)
IF (CAST(DATEDIFF(DAY, DATEADD(Year, @Age, @DOB), @Stamp) as int) &lt; 0) 
 SET @Age = @Age - 1
</code></pre>

blocks|key|1430494|text|尝试此解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430495|declare+@BirthDate+datetime
declare+@ToDate+datetime

set+@BirthDate+=+'1/3/1990'
set+@ToDate+=+'1/2/2008'
select+@BirthDate+[Date+of+Birth],+@ToDate+[ToDate],(case+when+(DatePart(mm,@ToDate)+<++Datepart(mm,@BirthDate))+
++++++++OR+(DatePart(m,@ToDate)+=+Datepart(m,@BirthDate)+AND+DatePart(dd,@ToDate)+<+Datepart(dd,@BirthDate))
++++++++then+(Datepart(yy,+@ToDate)+-+Datepart(yy,+@BirthDate)+-+1)
++++++++else+(Datepart(yy,+@ToDate)+-+Datepart(yy,+@BirthDate))end)+Age|code-block|syntax|javascript|1430496|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this solution:

<pre><code>declare @BirthDate datetime
declare @ToDate datetime

set @BirthDate = '1/3/1990'
set @ToDate = '1/2/2008'
select @BirthDate [Date of Birth], @ToDate [ToDate],(case when (DatePart(mm,@ToDate) &lt; Datepart(mm,@BirthDate)) 
 OR (DatePart(m,@ToDate) = Datepart(m,@BirthDate) AND DatePart(dd,@ToDate) &lt; Datepart(dd,@BirthDate))
 then (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate) - 1)
 else (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate))end) Age
</code></pre>

blocks|key|1430621|text|这将正确处理生日和舍入问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430622|DECLARE+@dob++datetime
SET+@dob='1992-01-09+00:00:00'

SELECT+DATEDIFF(YEAR,+'0:0',+getdate()-@dob)|code-block|syntax|javascript|1430623|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will correctly handle the issues with the birthday and rounding:

<pre><code>DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)
</code></pre>

blocks|key|1430724|text|Ed+Harper的解决方案是我发现的最简单的解决方案，当两个日期的月份和日期相差1天或更少时，它永远不会返回错误的答案。我做了一个小小的修改来处理负年龄。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430725|DECLARE+@D1+AS+DATETIME,+@D2+AS+DATETIME
SET+@D2+=+'2012-03-01+10:00:02'
SET+@D1+=+'2013-03-01+10:00:01'
SELECT
+++DATEDIFF(YEAR,+@D1,@D2)
+++%2B
+++CASE
++++++WHEN+@D1<@D2+AND+DATEADD(YEAR,+DATEDIFF(YEAR,@D1,+@D2),+@D1)+>+@D2
++++++THEN+-+1
++++++WHEN+@D1>@D2+AND+DATEADD(YEAR,+DATEDIFF(YEAR,@D1,+@D2),+@D1)+<+@D2
++++++THEN+1
++++++ELSE+0
+++END+AS+AGE|code-block|syntax|javascript|1430726|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Ed Harper's solution is the simplest I have found which never returns the wrong answer when the month and day of the two dates are 1 or less days apart. I made a slight modification to handle negative ages.

<pre><code>DECLARE @D1 AS DATETIME, @D2 AS DATETIME
SET @D2 = '2012-03-01 10:00:02'
SET @D1 = '2013-03-01 10:00:01'
SELECT
 DATEDIFF(YEAR, @D1,@D2)
 +
 CASE
 WHEN @D1&lt;@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) &gt; @D2
 THEN - 1
 WHEN @D1&gt;@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) &lt; @D2
 THEN 1
 ELSE 0
 END AS AGE
</code></pre>

blocks|key|1216098|text|Declare+@dob+datetime
Declare+@today+datetime

Set+@dob+=+'05/20/2000'
set+@today+=+getdate()

select++CASE
++++++++++++WHEN+dateadd(year,+datediff+(year,+@dob,+@today),+@dob)+>+@today+
++++++++++++THEN+datediff+(year,+@dob,+@today)+-+1
++++++++++++ELSE+datediff+(year,+@dob,+@today)
++++++++END+as+Age|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1216099|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Declare @dob datetime
Declare @today datetime

Set @dob = '05/20/2000'
set @today = getdate()

select CASE
 WHEN dateadd(year, datediff (year, @dob, @today), @dob) &gt; @today 
 THEN datediff (year, @dob, @today) - 1
 ELSE datediff (year, @dob, @today)
 END as Age
</code></pre>

blocks|key|1216112|text|CREATE+function+dbo.AgeAtDate(
++++@DOB++++datetime,
++++@CompareDate+datetime
)

returns+INT
as
begin

return+CASE+WHEN+@DOB+is+null
THEN+
++++null
ELSE+
DateDiff(yy,@DOB,+@CompareDate)+
-+CASE+WHEN+datepart(mm,@CompareDate)+>+datepart(mm,@DOB)+OR+(datepart(mm,@CompareDate)+=+datepart(mm,@DOB)+AND+datepart(dd,@CompareDate)+>=+datepart(dd,@DOB))
++THEN+0+
++ELSE+1
++END
END
End

GO|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1216113|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CREATE function dbo.AgeAtDate(
 @DOB datetime,
 @CompareDate datetime
)

returns INT
as
begin

return CASE WHEN @DOB is null
THEN 
 null
ELSE 
DateDiff(yy,@DOB, @CompareDate) 
- CASE WHEN datepart(mm,@CompareDate) &gt; datepart(mm,@DOB) OR (datepart(mm,@CompareDate) = datepart(mm,@DOB) AND datepart(dd,@CompareDate) &gt;= datepart(dd,@DOB))
 THEN 0 
 ELSE 1
 END
END
End

GO
</code></pre>

blocks|key|1216212|text|下面是我计算年龄的方法，给定出生日期和当前日期。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1216213|select+case+
++++++++++++when+cast(getdate()+as+date)+=+cast(dateadd(year,+(datediff(year,+'1996-09-09',+getdate())),+'1996-09-09')+as+date)
++++++++++++++++then+dateDiff(yyyy,'1996-09-09',dateadd(year,+0,+getdate()))
++++++++++++else+dateDiff(yyyy,'1996-09-09',dateadd(year,+-1,+getdate()))
++++++++end+as+MemberAge
go|code-block|syntax|javascript|1216214|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is how i calculate age given a birth date and current date.

<pre><code>select case 
 when cast(getdate() as date) = cast(dateadd(year, (datediff(year, '1996-09-09', getdate())), '1996-09-09') as date)
 then dateDiff(yyyy,'1996-09-09',dateadd(year, 0, getdate()))
 else dateDiff(yyyy,'1996-09-09',dateadd(year, -1, getdate()))
 end as MemberAge
go
</code></pre>

blocks|key|1216299|text|DECLARE+@FromDate+DATETIME+=+'1992-01-2623:59:59.000',+
++++++++@ToDate+++DATETIME+=+'2016-08-10+00:00:00.000',
++++++++@Years+INT,+@Months+INT,+@Days+INT,+@tmpFromDate+DATETIME
SET+@Years+=+DATEDIFF(YEAR,+@FromDate,+@ToDate)
+-+(CASE+WHEN+DATEADD(YEAR,+DATEDIFF(YEAR,+@FromDate,+@ToDate),
++++++++++@FromDate)+>+@ToDate+THEN+1+ELSE+0+END)+


SET+@tmpFromDate+=+DATEADD(YEAR,+@Years+,+@FromDate)
SET+@Months+=++DATEDIFF(MONTH,+@tmpFromDate,+@ToDate)
+-+(CASE+WHEN+DATEADD(MONTH,DATEDIFF(MONTH,+@tmpFromDate,+@ToDate),
++++++++++@tmpFromDate)+>+@ToDate+THEN+1+ELSE+0+END)+

SET+@tmpFromDate+=+DATEADD(MONTH,+@Months+,+@tmpFromDate)
SET+@Days+=++DATEDIFF(DAY,+@tmpFromDate,+@ToDate)
+-+(CASE+WHEN+DATEADD(DAY,+DATEDIFF(DAY,+@tmpFromDate,+@ToDate),
++++++++++@tmpFromDate)+>+@ToDate+THEN+1+ELSE+0+END)+

SELECT+@FromDate+FromDate,+@ToDate+ToDate,+
+++++++@Years+Years,++@Months+Months,+@Days+Days|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1216300|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DECLARE @FromDate DATETIME = '1992-01-2623:59:59.000', 
 @ToDate DATETIME = '2016-08-10 00:00:00.000',
 @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
 - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
 @FromDate) &gt; @ToDate THEN 1 ELSE 0 END) 


SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate)
 - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
 @tmpFromDate) &gt; @ToDate THEN 1 ELSE 0 END) 

SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
SET @Days = DATEDIFF(DAY, @tmpFromDate, @ToDate)
 - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
 @tmpFromDate) &gt; @ToDate THEN 1 ELSE 0 END) 

SELECT @FromDate FromDate, @ToDate ToDate, 
 @Years Years, @Months Months, @Days Days
</code></pre>

blocks|key|1216479|text|如果解决方案只有日期函数，而不是数学函数，也不关心闰年，那该怎么办？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1216480|CREATE+FUNCTION+dbo.getAge(@dt+datetime)+
RETURNS+int
AS
BEGIN
++++RETURN+
++++++++DATEDIFF(yy,+@dt,+getdate())
++++++++-+CASE+
++++++++++++WHEN+
++++++++++++++++MONTH(@dt)+>+MONTH(GETDATE())+OR+
++++++++++++++++(MONTH(@dt)+=+MONTH(GETDATE())+AND+DAY(@dt)+>+DAY(GETDATE()))+
++++++++++++THEN+1+
++++++++++++ELSE+0+
++++++++END
END|code-block|syntax|javascript|1216481|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

What about a solution with only date functions, not math, not worries about leap year

<pre><code>CREATE FUNCTION dbo.getAge(@dt datetime) 
RETURNS int
AS
BEGIN
 RETURN 
 DATEDIFF(yy, @dt, getdate())
 - CASE 
 WHEN 
 MONTH(@dt) &gt; MONTH(GETDATE()) OR 
 (MONTH(@dt) = MONTH(GETDATE()) AND DAY(@dt) &gt; DAY(GETDATE())) 
 THEN 1 
 ELSE 0 
 END
END
</code></pre>

blocks|key|1431427|text|这里有一种技术，似乎可以绕过边缘情况:年底和2月。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1431428|datediff(year,…,…)的问题是它只找到年份之间的差异，而不是实际日期，这在我看来有点天真。只有当出生日期是1月1日时，这才是可靠的。|offset|length|style|CODE|1431429|解决方案是将出生日期和询问日期的时间偏差相同的值。|1431430|由于闰年的问题，使用一年中的日期也是不可靠的，所以我使用月份和日期来做时间扭曲：|1431431|CREATE+FUNCTION+age(@then+AS+date,@now+AS+date)+RETURNS+int+AS
BEGIN
++++DECLARE+@month+INT+=+month(@then)-1;
++++DECLARE+@day+INT+=+day(@then)-1;

++++SET+@then=dateadd(month,-@month,@then);
++++SET+@then=dateadd(day,-@day,@then);

++++SET+@now=dateadd(month,-@month,@now;)
++++SET+@now=dateadd(day,-@day,@now);

++++RETURN+datediff(year,@then,@now);
END;|code-block|syntax|javascript|1431432|当然，您可以编写得更紧凑。你甚至可以把它写成一行，如果这是你想玩得开心的话：|1431433|CREATE+FUNCTION+age(@then+AS+date,@now+AS+date)+RETURNS+int+AS
BEGIN
++++RETURN++datediff(
++++++++year,
++++++++dateadd(day,-day(@then)%2B1,dateadd(month,-month(@then)%2B1,@then)),
++++++++dateadd(day,-day(@then)%2B1,dateadd(month,-month(@then)%2B1,@now))
++++);
END;|1431434|但这只是在炫耀，而且效率较低，因为它需要重新计算偏移量。|1431435|entityMap^0|0|0|I|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@$D|10|E|11|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|14|8|@]|9|@]|A|$O|P]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|N|7|16|8|@]|9|@]|A|$O|P]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

Here is a technique which seems to work around the edge cases: the end of the year and leap Februaries.
The problem with <code>datediff(year,…,…)</code> is that it only finds the difference between the years, and not the actual dates, which is a bit naïve in my opinion. This is only reliable if the date of birth is 1st Jan.
The solution is to time warp the date of birth to the first of January, and the asking date by the same amount.
Because of the leap year problem using the day of year is also unreliable, so I use the month and day to do the time warp:
<pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION age(@then AS date,@now AS date) RETURNS int AS
BEGIN
 DECLARE @month INT = month(@then)-1;
 DECLARE @day INT = day(@then)-1;

 SET @then=dateadd(month,-@month,@then);
 SET @then=dateadd(day,-@day,@then);

 SET @now=dateadd(month,-@month,@now;)
 SET @now=dateadd(day,-@day,@now);

 RETURN datediff(year,@then,@now);
END;
</code></pre>
You can write this more compactly, of course. You can even write it in one line, if that’s your idea of having a good time:
<pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION age(@then AS date,@now AS date) RETURNS int AS
BEGIN
 RETURN datediff(
 year,
 dateadd(day,-day(@then)+1,dateadd(month,-month(@then)+1,@then)),
 dateadd(day,-day(@then)+1,dateadd(month,-month(@then)+1,@now))
 );
END;
</code></pre>
but that’s only showing off, and it’s less efficient as it needs to recalculate the offsets.

blocks|key|1216574|text|declare+@birthday+as+datetime
set+@birthday+=+'2000-01-01'
declare+@today+as+datetime
set+@today+=+GetDate()
select+
++++case+when+(+substring(convert(varchar,+@today,+112),+5,4)+>=+substring(convert(varchar,+@birthday,+112),+5,4)++)+then
++++++++(datepart(year,@today)+-+datepart(year,@birthday))
++++else+
++++++++(datepart(year,@today)+-+datepart(year,@birthday))+-+1
++++end|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1216575|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>declare @birthday as datetime
set @birthday = '2000-01-01'
declare @today as datetime
set @today = GetDate()
select 
 case when ( substring(convert(varchar, @today, 112), 5,4) &gt;= substring(convert(varchar, @birthday, 112), 5,4) ) then
 (datepart(year,@today) - datepart(year,@birthday))
 else 
 (datepart(year,@today) - datepart(year,@birthday)) - 1
 end
</code></pre>

blocks|key|1216619|text|下面的脚本检查现在和给定出生日期之间的年份差异；第二部分检查当前年份的生日是否已经过去；如果不是，则减去生日：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1216620|SELECT+year(NOW())+-+year(date_of_birth)+-+(CONCAT(year(NOW()),+'-',+month(date_of_birth),+'-',+day(date_of_birth))+>+NOW())+AS+Age
FROM+tableName;|code-block|syntax|javascript|1216621|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The following script checks the difference in years between now and the given date of birth; the second part checks whether the birthday is already past in the current year; if not, it subtracts it:
<pre><code>SELECT year(NOW()) - year(date_of_birth) - (CONCAT(year(NOW()), '-', month(date_of_birth), '-', day(date_of_birth)) &gt; NOW()) AS Age
FROM tableName;
</code></pre>

blocks|key|1430807|text|select+datediff(day,'1991-03-16',getdate())+\\for+days,get+date+refers+today+date
select+datediff(year,'1991-03-16',getdate())+\\for+years
select+datediff(month,'1991-03-16',getdate())+\\for+month|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1430808|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select datediff(day,'1991-03-16',getdate()) \\for days,get date refers today date
select datediff(year,'1991-03-16',getdate()) \\for years
select datediff(month,'1991-03-16',getdate()) \\for month
</code></pre>

blocks|key|1216159|text|你应该用下面的方法来计算年份：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1216160|select+cast(datediff(DAY,+'2000-03-01+10:00:01',+'2013-03-01+10:00:00')+/+(365.23076923074)+as+int)+as+'Age'|code-block|syntax|javascript|1216161|这很简单..。|1216162|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

you should count years by following way :-

<pre><code>select cast(datediff(DAY, '2000-03-01 10:00:01', '2013-03-01 10:00:00') / (365.23076923074) as int) as 'Age'
</code></pre>

it's very easy...

blocks|key|1430789|text|我们使用了类似于这里的东西，然后取平均年龄：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430790|ROUND(avg(CONVERT(int,DATEDIFF(hour,DOB,GETDATE())/8766.0)),0)+AS+AverageAge|code-block|syntax|javascript|1430791|请注意，圆是在外面而不是在里面。这将允许AVG更准确，我们只需舍入一次。让它变得更快。|1430792|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

We used something like here, but then taking the average age:

<pre><code>ROUND(avg(CONVERT(int,DATEDIFF(hour,DOB,GETDATE())/8766.0)),0) AS AverageAge
</code></pre>

Notice, the ROUND is outside rather than inside. This will allow for the AVG to be more accurate and we ROUND only once. Making it faster too.

blocks|key|1430822|text|select+DATEDIFF(yy,@DATE,GETDATE())+-
case+when+DATEPART(mm,GETDATE())*100%2BDATEPART(dd,GETDATE())>=
DATEPART(mm,@DATE)*100%2BDATEPART(dd,@DATE)+THEN+0
ELSE+1+END+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1430823|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select DATEDIFF(yy,@DATE,GETDATE()) -
case when DATEPART(mm,GETDATE())*100+DATEPART(dd,GETDATE())&gt;=
DATEPART(mm,@DATE)*100+DATEPART(dd,@DATE) THEN 0
ELSE 1 END 
</code></pre>

blocks|key|1430835|text|SELECT+CAST(DATEDIFF(dy,+@DOB,+GETDATE()%2B1)/365.25+AS+int)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1430836|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT CAST(DATEDIFF(dy, @DOB, GETDATE()+1)/365.25 AS int)
</code></pre>

blocks|key|1430934|text|你应该使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1430935|从table_name中选择楼层(DATEDIFF(CURDATE()，DATE(道布))/365.25)；|1430936|这里CURDATE()使用当前日期您可以用'+yyyy-mm-dd‘格式给出自己的日期日期(道布)从您的列中提取yyyy-mm-dd年份这里是您的列名(但您应该更改table以将数据类型修改为DATETIME，在本例中为nvarchar)注意-此查询在mysql中使用此返回年限为整年|1430937|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

you should use 
select FLOOR(DATEDIFF(CURDATE(),DATE(DOB))/365.25) from table_name;
 
here CURDATE() uses current date you can give own date in 'yyyy-mm-dd' format
DATE(DOB) extract yyyy-mm-dd year from your column which is in DATETIME format
 here DOB is your column name (but you should alter table to modify the data type to be DATETIME in your case which is nvarchar)
Note-
this query is used in mysql
this return age in whole year

blocks|key|1430961|text|DECLARE+@yourBirthDate+DATETIME+=+'1987-05-25'
SELECT+YEAR(DATEADD(DAY,+DATEDIFF(DAY,+@yourBirthDate,+GETDATE()),+CAST('0001-01-01'+AS+DATETIME2)))+-+1|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1430962|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DECLARE @yourBirthDate DATETIME = '1987-05-25'
SELECT YEAR(DATEADD(DAY, DATEDIFF(DAY, @yourBirthDate, GETDATE()), CAST('0001-01-01' AS DATETIME2))) - 1
</code></pre>

I have a table listing people along with their date of birth (currently a nvarchar(25))

How can I convert that to a date, and then calculate their age in years?

My data looks as follows

<pre><code>ID Name DOB
1 John 1992-01-09 00:00:00
2 Sally 1959-05-20 00:00:00
</code></pre>

I would like to see:

<pre><code>ID Name AGE DOB
1 John 17 1992-01-09 00:00:00
2 Sally 50 1959-05-20 00:00:00
</code></pre>

How to calculate age (in years) based on Date of Birth and getDate()

我有一个表，列出了人们及其出生日期(目前是nvarchar(25))如何将其转换为日期，然后以年为单位计算它们的年龄？我的数据如下所示ID    Name   DOB1     John   1992-01-09 00:00:002     Sally  1959-05-20 00:00:00我希望看到：ID    Name   AGE  DOB1     John   17   1992-01-

问如何根据出生日期和getDate()计算年龄(以年为单位)
EN

回答 30

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何根据出生日期和getDate()计算年龄(以年为单位)EN

回答 30

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何根据出生日期和getDate()计算年龄(以年为单位)
EN