datediff函数不能很好地处理年份界限，而且要获取月份和分开的日子将是熊。我知道我可以在客户端相对容易地做到这一点，但是我想在我的存储过程中完成它。#1 楼
这是一些T-SQL，可为您提供@date中指定日期以来的年，月和天数。它考虑到DATEDIFF（）计算差异而没有考虑是哪个月或一天的事实（因此8/31和9/1之间的月份差异为1个月），并使用case语句处理该差异，从而减少结果，其中适当。DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '2/29/04'
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 @years, @months, @days
评论
上面的简短语法注释是错误的；需要单独检查更大的月份，然后如果月份相同，则进行当天检查
–LMK
14年6月2日在20:54
是的，如果当前日期是该月的30号，而要评估的日期是下个月的1号，则缩短的语法将无法正常工作。
–培根片
2014年6月3日，12：50
临时日期变量的原因是什么？ @@ tmpdate和@@ date都在两个表达式中使用。与仅使用一种方法相比，这种方法有什么不同之处吗？
–rdans
18年5月22日在9:34
#2 楼
试试这个...SELECT CASE WHEN
(DATEADD(year,DATEDIFF(year, @datestart ,@dateend) , @datestart) > @dateend)
THEN DATEDIFF(year, @datestart ,@dateend) -1
ELSE DATEDIFF(year, @datestart ,@dateend)
END
基本上是“ DateDiff（year ...”），它会告诉您该人今年的年龄，所以我只需添加一个案例声明，如果他们今年还没有过生日，则减去1年，否则返回该值。
评论
如果您只需要自该日期起的年份，这将很有用。
–达沃斯
2012年11月12日，0：51
较短的版本SELECT DATEDIFF（year，@datestart，@ dateend）+ CASE WHEN（DATEADD（year，DATEDIFF（year，@datestart，@ dateend），@datestart）> @dateend）然后-1 ELSE 0 END
–彼得
17年2月9日在8:30
#3 楼
通过文本获取年龄的简单方法如下：Select cast((DATEDIFF(m, date_of_birth, GETDATE())/12) as varchar) + ' Y & ' +
cast((DATEDIFF(m, date_of_birth, GETDATE())%12) as varchar) + ' M' as Age
结果格式将是：
**63 Y & 2 M**
#4 楼
用ISO格式的日期通过算术实现。declare @now date,@dob date, @now_i int,@dob_i int, @days_in_birth_month int
declare @years int, @months int, @days int
set @now = '2013-02-28'
set @dob = '2012-02-29' -- Date of Birth
set @now_i = convert(varchar(8),@now,112) -- iso formatted: 20130228
set @dob_i = convert(varchar(8),@dob,112) -- iso formatted: 20120229
set @years = ( @now_i - @dob_i)/10000
-- (20130228 - 20120229)/10000 = 0 years
set @months =(1200 + (month(@now)- month(@dob))*100 + day(@now) - day(@dob))/100 %12
-- (1200 + 0228 - 0229)/100 % 12 = 11 months
set @days_in_birth_month = day(dateadd(d,-1,left(convert(varchar(8),dateadd(m,1,@dob),112),6)+'01'))
set @days = (sign(day(@now) - day(@dob))+1)/2 * (day(@now) - day(@dob))
+ (sign(day(@dob) - day(@now))+1)/2 * (@days_in_birth_month - day(@dob) + day(@now))
-- ( (-1+1)/2*(28 - 29) + (1+1)/2*(29 - 29 + 28))
-- Explain: if the days of now is bigger than the days of birth, then diff the two days
-- else add the days of now and the distance from the date of birth to the end of the birth month
select @years,@months,@days -- 0, 11, 28
测试用例
天的方法与公认的答案不同，不同之处在表中显示。以下评论：
dob now years months days
2012-02-29 2013-02-28 0 11 28 --Days will be 30 if calculated by the approach in accepted answer.
2012-02-29 2016-02-28 3 11 28 --Days will be 31 if calculated by the approach in accepted answer, since the day of birth will be changed to 28 from 29 after dateadd by years.
2012-02-29 2016-03-31 4 1 2
2012-01-30 2016-02-29 4 0 30
2012-01-30 2016-03-01 4 1 2 --Days will be 1 if calculated by the approach in accepted answer, since the day of birth will be changed to 30 from 29 after dateadd by years.
2011-12-30 2016-02-29 4 1 30
Days的简短版本：
set @days = CASE WHEN day(@now) >= day(@dob) THEN day(@now) - day(@dob)
ELSE @days_in_birth_month - day(@dob) + day(@now) END
如果需要仅年龄和月份的年龄，它可能会更简单
set @years = ( @now_i/100 - @dob_i/100)/100
set @months =(12 + month(@now) - month(@dob))%12
select @years,@months -- 1, 0
注意：SQL Server日期格式的一个非常有用的链接
#5 楼
这是一个（略）简单的版本：CREATE PROCEDURE dbo.CalculateAge
@dayOfBirth datetime
AS
DECLARE @today datetime, @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int
SELECT @today = GETDATE()
SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)
SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)
SELECT @months = MONTH(@today - @thisYearBirthDay) - 1
SELECT @days = DAY(@today - @thisYearBirthDay) - 1
SELECT @years, @months, @days
GO
#6 楼
与功能相同。create function [dbo].[Age](@dayOfBirth datetime, @today datetime)
RETURNS varchar(100)
AS
Begin
DECLARE @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int
set @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)
set @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)
set @months = MONTH(@today - @thisYearBirthDay) - 1
set @days = DAY(@today - @thisYearBirthDay) - 1
return cast(@years as varchar(2)) + ' years,' + cast(@months as varchar(2)) + ' months,' + cast(@days as varchar(3)) + ' days'
end
评论
SELECT dbo.age（'2013-03-31'，'2014-01-31'）返回10m2d。
–托尼
11年10月10日在22:51
#7 楼
create procedure getDatedifference
(
@startdate datetime,
@enddate datetime
)
as
begin
declare @monthToShow int
declare @dayToShow int
--set @startdate='01/21/1934'
--set @enddate=getdate()
if (DAY(@startdate) > DAY(@enddate))
begin
set @dayToShow=0
if (month(@startdate) > month(@enddate))
begin
set @monthToShow= (12-month(@startdate)+ month(@enddate)-1)
end
else if (month(@startdate) < month(@enddate))
begin
set @monthToShow= ((month(@enddate)-month(@startdate))-1)
end
else
begin
set @monthToShow= 11
end
-- set @monthToShow= convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))-1
if(@monthToShow<0)
begin
set @monthToShow=0
end
declare @amonthbefore integer
set @amonthbefore=Month(@enddate)-1
if(@amonthbefore=0)
begin
set @amonthbefore=12
end
if (@amonthbefore in(1,3,5,7,8,10,12))
begin
set @dayToShow=31-DAY(@startdate)+DAY(@enddate)
end
if (@amonthbefore=2)
begin
IF (YEAR( @enddate ) % 4 = 0 AND YEAR( @enddate ) % 100 != 0) OR YEAR( @enddate ) % 400 = 0
begin
set @dayToShow=29-DAY(@startdate)+DAY(@enddate)
end
else
begin
set @dayToShow=28-DAY(@startdate)+DAY(@enddate)
end
end
if (@amonthbefore in (4,6,9,11))
begin
set @dayToShow=30-DAY(@startdate)+DAY(@enddate)
end
end
else
begin
--set @monthToShow=convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))
if (month(@enddate)< month(@startdate))
begin
set @monthToShow=12+(month(@enddate)-month(@startdate))
end
else
begin
set @monthToShow= (month(@enddate)-month(@startdate))
end
set @dayToShow=DAY(@enddate)-DAY(@startdate)
end
SELECT
FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25) as [yearToShow],
@monthToShow as monthToShow ,@dayToShow as dayToShow ,
convert(varchar,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25)) +' Year ' + convert(varchar,@monthToShow) +' months '+convert(varchar,@dayToShow)+' days ' as age
return
end
#8 楼
我使用了我修改过的函数（“天”部分），来自@Dane答案：https://stackoverflow.com/a/57720/2097023CREATE FUNCTION dbo.EdadAMD
(
@FECHA DATETIME
)
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE
@tmpdate DATETIME
, @years INT
, @months INT
, @days INT
, @EdadAMD NVARCHAR(10);
SELECT @tmpdate = @FECHA;
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE
WHEN (MONTH(@FECHA) > MONTH(GETDATE()))
OR (
MONTH(@FECHA) = MONTH(GETDATE())
AND DAY(@FECHA) > DAY(GETDATE())
) THEN
1
ELSE
0
END;
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate);
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE
WHEN DAY(@FECHA) > DAY(GETDATE()) THEN
1
ELSE
0
END;
SELECT @tmpdate = DATEADD(m, @months, @tmpdate);
IF MONTH(@FECHA) = MONTH(GETDATE())
AND DAY(@FECHA) > DAY(GETDATE())
SELECT @days =
DAY(EOMONTH(GETDATE(), -1)) - (DAY(@FECHA) - DAY(GETDATE()));
ELSE
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE());
SELECT @EdadAMD = CONCAT(@years, 'a', @months, 'm', @days, 'd');
RETURN @EdadAMD;
END;
GO
它运行得很好。
#9 楼
我已经看过几次这个问题，结果输出的是Years，Month，Days，但是从来没有数字/十进制结果。 （至少没有一个不会四舍五入）。我欢迎您对此功能提供反馈。可能仍不需要稍作调整。
-函数的输入是两个日期。
-输出是两个日期之间的数字年份，以Decimal（7,4）表示。格式。
-输出始终是一个正数。
-注：如果差异大于999.9999，则输出将无法处理。
-逻辑基于三个步骤。
-1）差异是多少？少于1年（0.5000、0.3333、0.6667等）
-2）差异是否恰好是整数年（1,2,3等）
- -3）（其他）...不同之处在于年份和天数。 （1.5000、2.3333、7.6667等）
CREATE Function [dbo].[F_Get_Actual_Age](@pi_date1 datetime,@pi_date2 datetime)
RETURNS Numeric(7,4)
AS
BEGIN
Declare
@l_tmp_date DATETIME
,@l_days1 DECIMAL(9,6)
,@l_days2 DECIMAL(9,6)
,@l_result DECIMAL(10,6)
,@l_years DECIMAL(7,4)
--Check to make sure there is a date for both inputs
IF @pi_date1 IS NOT NULL and @pi_date2 IS NOT NULL
BEGIN
IF @pi_date1 > @pi_date2 --Make sure the "older" date is in @pi_date1
BEGIN
SET @l_tmp_date = @pi_date2
SET @pi_date2 = @Pi_date1
SET @pi_date1 = @l_tmp_date
END
--Check #1 If date1 + 1 year is greater than date2, difference must be less than 1 year
IF DATEADD(YYYY,1,@pi_date1) > @pi_date2
BEGIN
--How many days between the two dates (numerator)
SET @l_days1 = DATEDIFF(dd,@pi_date1, @pi_date2)
--subtract 1 year from date2 and calculate days bewteen it and date2
--This is to get the denominator and accounts for leap year (365 or 366 days)
SET @l_days2 = DATEDIFF(dd,dateadd(yyyy,-1,@pi_date2),@pi_date2)
SET @l_years = @l_days1 / @l_days2 -- Do the math
END
ELSE
--Check #2 Are the dates an exact number of years apart.
--Calculate years bewteen date1 and date2, then add the years to date1, compare dates to see if exactly the same.
IF DATEADD(YYYY,DATEDIFF(YYYY,@pi_date1,@pi_date2),@pi_date1) = @pi_date2
SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2) --AS Years, 'Exactly even Years' AS Msg
ELSE
BEGIN
--Check #3 The rest of the cases.
--Check if datediff, returning years, over or under states the years difference
SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2)
IF DATEADD(YYYY,@l_years,@pi_date1) > @pi_date2
SET @l_years = @l_years -1
--use basicly same logic as in check #1
SET @l_days1 = DATEDIFF(dd,DATEADD(YYYY,@l_years,@pi_date1), @pi_date2)
SET @l_days2 = DATEDIFF(dd,dateadd(yyyy,-1,@pi_date2),@pi_date2)
SET @l_years = @l_years + @l_days1 / @l_days2
--SELECT @l_years AS Years, 'Years Plus' AS Msg
END
END
ELSE
SET @l_years = 0 --If either date was null
RETURN @l_Years --Return the result as decimal(7,4)
END
`
#10 楼
很老的问题，但我想分享一下我计算年龄时所做的事情 Declare @BirthDate As DateTime
Set @BirthDate = '1994-11-02'
SELECT DATEDIFF(YEAR,@BirthDate,GETDATE()) - (CASE
WHEN MONTH(@BirthDate)> MONTH(GETDATE()) THEN 1
WHEN MONTH(@BirthDate)= MONTH(GETDATE()) AND DAY(@BirthDate) > DAY(GETDATE()) THEN 1
Else 0 END)
#11 楼
这是一种简单的方法，基于BUT两天之间的小时数，且结束日期被截断。SELECT CAST(DATEDIFF(hour,Birthdate,CAST(GETDATE() as Date))/8766.0 as INT) AS Age FROM <YourTable>
已被证明非常准确和可靠。如果不是在GETDATE（）上使用内部CAST，则它可能会在午夜之前几个小时翻转生日，但是使用CAST，由于年龄恰好在午夜改变，所以生日已经过去了。
#12 楼
您是否要计算年龄的总天数/月数/年数？您有开始日期吗？还是要剖析（例如：24年零1个月29天）？如果您使用的是开始日期，datediff将输出总天/月/年使用以下命令：
Select DateDiff(d,'1984-07-12','2008-09-11')
Select DateDiff(m,'1984-07-12','2008-09-11')
Select DateDiff(yyyy,'1984-07-12','2008-09-11')
，相应的输出为（8827/290/24）。
现在，如果您想使用解剖方法，您必须减去以天为单位的天数（天-365 *年），然后对其进行进一步的数学运算以得出月数，等等。
评论
当我调用SELECT DateDiff（yyyy，'1984-07-12'，'2008-09-11'）AS'age'FROM tbl_product;得到错误：未捕获错误：没有这样的列：yyyy
– Subin Babu
2月14日8:23
#13 楼
这是SQL代码，为您提供自sysdate起的年数，月数和天数。以这种格式（dd_mon_yy）输入input_birth_date的值。注意：输入年，月和日的相同值（出生日期），例如85年3月1日
select trunc((sysdate -to_date('&input_birth_date_dd_mon_yy'))/365) years,
trunc(mod(( sysdate -to_date('&input_birth_date_dd_mon_yy'))/365,1)*12) months,
trunc((mod((mod((sysdate -to_date('&input_birth_date_dd_mon_yy'))/365,1)*12),1)*30)+1) days
from dual
评论
这是Oracle PL / SQL语法，而不是T-SQL。它还假设年份始终为365天，月份始终为30天
–达沃斯
2012年11月12日，0：54
#14 楼
T-SQL中的DateTime值存储为浮点数。您可以相互减去日期，现在有了一个新的日期，即它们之间的时间跨度。declare @birthdate datetime
set @birthdate = '6/15/1974'
--age in years - short version
print year(getdate() - @birthdate) - year(0)
--age in years - visualization
declare @mindate datetime
declare @span datetime
set @mindate = 0
set @span = getdate() - @birthdate
print @mindate
print @birthdate
print getdate()
print @span
--substract minyear from spanyear to get age in years
print year(@span) - year(@mindate)
print month(@span)
print day(@span)
评论
我想您还需要从月份和日期中减去1。 （实际上，您将分别减去month（@span）和day（@span），这与减去year（@span）一致）。此答案与Leonardo的答案相同，后者也由simon831以函数的形式重新发布。无论如何，这是不可靠的。准确性/错误似乎取决于当月的天数以及@birthdate月的天数。今天是4/5/2012，我尝试了您对set @birthdate ='4/5/1974'的建议，并得到38 0 1，即使它应该是38 0 0。
– Andriy M
2012年4月5日在16:17
#15 楼
CREATE FUNCTION DBO.GET_AGE
(
@DATE AS DATETIME
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @YEAR AS VARCHAR(50) = ''
DECLARE @MONTH AS VARCHAR(50) = ''
DECLARE @DAYS AS VARCHAR(50) = ''
DECLARE @RESULT AS VARCHAR(MAX) = ''
SET @YEAR = CONVERT(VARCHAR,(SELECT DATEDIFF(MONTH,CASE WHEN DAY(@DATE) > DAY(GETDATE()) THEN DATEADD(MONTH,1,@DATE) ELSE @DATE END,GETDATE()) / 12 ))
SET @MONTH = CONVERT(VARCHAR,(SELECT DATEDIFF(MONTH,CASE WHEN DAY(@DATE) > DAY(GETDATE()) THEN DATEADD(MONTH,1,@DATE) ELSE @DATE END,GETDATE()) % 12 ))
SET @DAYS = DATEDIFF(DD,DATEADD(MM,CONVERT(INT,CONVERT(INT,@YEAR)*12 + CONVERT(INT,@MONTH)),@DATE),GETDATE())
SET @RESULT = (RIGHT('00' + @YEAR, 2) + ' YEARS ' + RIGHT('00' + @MONTH, 2) + ' MONTHS ' + RIGHT('00' + @DAYS, 2) + ' DAYS')
RETURN @RESULT
END
SELECT DBO.GET_AGE('04/12/1986')
#16 楼
DECLARE @BirthDate datetime, @AgeInMonths int
SET @BirthDate = '10/5/1971'
SET @AgeInMonths -- Determine the age in "months old":
= DATEDIFF(MONTH, @BirthDate, GETDATE()) -- .Get the difference in months
- CASE WHEN DATEPART(DAY,GETDATE()) -- .If today was the 1st to 4th,
< DATEPART(DAY,@BirthDate) -- (or before the birth day of month)
THEN 1 ELSE 0 END -- ... don't count the month.
SELECT @AgeInMonths / 12 as AgeYrs -- Divide by 12 months to get the age in years
,@AgeInMonths % 12 as AgeXtraMonths -- Get the remainder of dividing by 12 months = extra months
,DATEDIFF(DAY -- For the extra days, find the difference between,
,DATEADD(MONTH, @AgeInMonths -- 1. Last Monthly Birthday
, @BirthDate) -- (if birthdays were celebrated monthly)
,GETDATE()) as AgeXtraDays -- 2. Today's date.
评论
要在您的答案中包含代码块，请将其缩进4个空格，以使其正确格式化。我已编辑您的帖子以符合此要求。
– elmart
13年8月29日在17:52
#17 楼
对于那些想要在表中创建计算列以存储年龄的用户：CASE WHEN DateOfBirth< DATEADD(YEAR, (DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, DateOfBirth))*-1, GETDATE())
THEN DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, DateOfBirth)
ELSE DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, DateOfBirth) -1 END
#18 楼
这是给定生日和当前日期的年龄的计算方法。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
#19 楼
还有另一种计算年龄的方法是见下表
FirstName LastName DOB
sai krishnan 1991-11-04
Harish S A 1998-10-11
要查找年龄，您可以计算到整个月
Select datediff(MONTH,DOB,getdate())/12 as dates from [Organization].[Employee]
结果将是
firstname dates
sai 27
Harish 20
#20 楼
declare @StartDate datetime = '2016-01-31'
declare @EndDate datetime = '2016-02-01'
SELECT @StartDate AS [StartDate]
,@EndDate AS [EndDate]
,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END AS [Years]
,DATEDIFF(Month,(DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate)),@EndDate) - CASE WHEN DATEADD(Month, DATEDIFF(Month,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @EndDate THEN 1 ELSE 0 END AS [Months]
,DATEDIFF(Day, DATEADD(Month,DATEDIFF(Month, (DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate)),@EndDate) - CASE WHEN DATEADD(Month, DATEDIFF(Month,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @EndDate THEN 1 ELSE 0 END ,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate)) ,@EndDate) - CASE WHEN DATEADD(Day,DATEDIFF(Day, DATEADD(Month,DATEDIFF(Month, (DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate)),@EndDate) - CASE WHEN DATEADD(Month, DATEDIFF(Month,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @EndDate THEN 1 ELSE 0 END ,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate)) ,@EndDate),DATEADD(Month,DATEDIFF(Month, (DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate)),@EndDate) - CASE WHEN DATEADD(Month, DATEDIFF(Month,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @EndDate THEN 1 ELSE 0 END ,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate))) > @EndDate THEN 1 ELSE 0 END AS [Days]
#21 楼
select DOB as Birthdate,
YEAR(GETDATE()) as ThisYear,
YEAR(getdate()) - EAR(date1) as Age
from TableName
评论
@Gvidas EAR（date1）？这是内置函数吗？
– HABO
18年9月4日在18:57
#22 楼
SELECT DOB AS Birthdate ,
YEAR(GETDATE()) AS ThisYear,
YEAR(getdate()) - YEAR(DOB) AS Age
FROM tableprincejain
评论
尽管此代码段可以解决问题，但并未说明原因或答案。请附上代码说明，因为这确实有助于提高您的帖子质量。请记住，您将来会为读者回答这个问题，而这些人可能不知道您提出代码建议的原因。举报人/审阅者：对于仅限代码的答案，例如这一答案，请表决，不要删除！
–帕特里克（Patrick）
17年8月31日在8:22
#23 楼
DECLARE @DoB AS DATE = '1968-10-24'
DECLARE @cDate AS DATE = CAST('2000-10-23' AS DATE)
SELECT
--Get Year difference
DATEDIFF(YEAR,@DoB,@cDate) -
--Cases where year difference will be augmented
CASE
--If Date of Birth greater than date passed return 0
WHEN YEAR(@DoB) - YEAR(@cDate) >= 0 THEN DATEDIFF(YEAR,@DoB,@cDate)
--If date of birth month less than date passed subtract one year
WHEN MONTH(@DoB) - MONTH(@cDate) > 0 THEN 1
--If date of birth day less than date passed subtract one year
WHEN MONTH(@DoB) - MONTH(@cDate) = 0 AND DAY(@DoB) - DAY(@cDate) > 0 THEN 1
--All cases passed subtract zero
ELSE 0
END
#24 楼
declare @BirthDate datetime
declare @TotalYear int
declare @TotalMonths int
declare @TotalDays int
declare @TotalWeeks int
declare @TotalHours int
declare @TotalMinute int
declare @TotalSecond int
declare @CurrentDtTime datetime
set @BirthDate='1998/01/05 05:04:00' -- Set Your date here
set @TotalYear= FLOOR(DATEDIFF(DAY, @BirthDate, GETDATE()) / 365.25)
set @TotalMonths= FLOOR(DATEDIFF(DAY,DATEADD(year, @TotalYear,@BirthDate),GetDate()) / 30.436875E)
set @TotalDays= FLOOR(DATEDIFF(DAY, DATEADD(month, @TotalMonths,DATEADD(year,
@TotalYear,@BirthDate)), GETDATE()))
set @CurrentDtTime=CONVERT(datetime,CONVERT(varchar(50), DATEPART(year,
GetDate()))+'/' +CONVERT(varchar(50), DATEPART(MONTH, GetDate()))
+'/'+ CONVERT(varchar(50),DATEPART(DAY, GetDate()))+' '
+ CONVERT(varchar(50),DATEPART(HOUR, @BirthDate))+':'+
CONVERT(varchar(50),DATEPART(MINUTE, @BirthDate))+
':'+ CONVERT(varchar(50),DATEPART(Second, @BirthDate)))
set @TotalHours = DATEDIFF(hour, @CurrentDtTime, GETDATE())
if(@TotalHours < 0)
begin
set @TotalHours = DATEDIFF(hour,DATEADD(Day,-1, @CurrentDtTime), GETDATE())
set @TotalDays= @TotalDays -1
end
set @TotalMinute= DATEPART(MINUTE, GETDATE())-DATEPART(MINUTE, @BirthDate)
if(@TotalMinute < 0)
set @TotalMinute = DATEPART(MINUTE, DATEADD(hour,-1,GETDATE()))+(60-DATEPART(MINUTE,
@BirthDate))
set @TotalSecond= DATEPART(Second, GETDATE())-DATEPART(Second, @BirthDate)
Print 'Your age are'+ CHAR(13)
+ CONVERT(varchar(50), @TotalYear)+' Years, ' +
CONVERT(varchar(50),@TotalMonths) +' Months, ' +
CONVERT(varchar(50),@TotalDays)+' Days, ' +
CONVERT(varchar(50),@TotalHours)+' Hours, ' +
CONVERT(varchar(50),@TotalMinute)+' Minutes, ' +
CONVERT(varchar(50),@TotalSecond)+' Seconds. ' +char(13)+
'Your are born at day of week was - ' + CONVERT(varchar(50),DATENAME(dw ,
@BirthDate ))
+char(13)+char(13)+
+'Your Birthdate to till date your '+ CHAR(13)
+'Years - ' + CONVERT(varchar(50), FLOOR(DATEDIFF(DAY, @BirthDate, GETDATE()) /
365.25))
+' , Months - ' + CONVERT(varchar(50),DATEDIFF(MM,@BirthDate,getdate()))
+' , Weeks - ' + CONVERT(varchar(50),DATEDIFF(wk,@BirthDate,getdate()))
+' , Days - ' + CONVERT(varchar(50),DATEDIFF(dd,@BirthDate,getdate()))+char(13)+
+'Hours - ' + CONVERT(varchar(50),DATEDIFF(HH,@BirthDate,getdate()))
+' , Minutes - ' + CONVERT(varchar(50),DATEDIFF(mi,@BirthDate,getdate()))
+' , Seconds - ' + CONVERT(varchar(50),DATEDIFF(ss,@BirthDate,getdate()))
输出
Your age are
22 Years, 0 Months, 2 Days, 11 Hours, 30 Minutes, 16 Seconds.
Your are born at day of week was - Monday
Your Birthdate to till date your
Years - 22 , Months - 264 , Weeks - 1148 , Days - 8037
Hours - 192899 , Minutes - 11573970 , Seconds - 694438216
评论
请查看有关javascript年龄问题的答案，该问题讨论了提出适合人类的选择年，月和日的方式有多么困难。我将在下一条评论中摘录。“如果您将2月2日至3月2日计算为一个月，则为1个月29天。但是，如果从1月2日至3月1日又如何呢？那两者之间的间隔天数相同。现在是1个月（对于所有4月）+ 3月1天+ 1月1月31日，共1个月32天？您是否希望您的月份与实际日历重合，以便人类可以用手指回溯并从中获取正确的日期？比您想象的要难得多。“