当前位置： 文江博客话题详情

如何在 T-SQL 中用年、月、日计算年龄

发布于 2024-07-04 07:48:12 字数 242 浏览 6 评论 0原文

在 T-SQL (SQL Server 2000) 中计算某人年龄（以年、月和日为单位）的最佳方法是什么？

datediff 函数不能很好地处理年份边界，而且将月份和日期分开也很麻烦。 我知道我可以相对轻松地在客户端完成此操作，但我希望在我的 存储中完成此操作程序。

原文

What would be the best way to calculate someone's age in years, months, and days in T-SQL (SQL Server 2000)?

The datediff function doesn't handle year boundaries well, plus getting the months and days separate will be a bear. I know I can do it on the client side relatively easily, but I'd like to have it done in my stored procedure.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（27）

記憶穿過時間隧道 2024-07-11 07:48:12

获取文本年龄的简单方法如下：

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**

Simple way to get age as text is as below:

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

Results Format will be:

**63 Y & 2 M**
回复 原文
随心而道 2024-07-11 07:48:12

下面是一些 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

Here is some T-SQL that gives you the number of years, months, and days since the day specified in @date. It takes into account the fact that DATEDIFF() computes the difference without considering what month or day it is (so the month diff between 8/31 and 9/1 is 1 month) and handles that with a case statement that decrements the result where appropriate.

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
回复 原文
清旖 2024-07-11 07:48:12
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.

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.
回复 原文
谁与争疯 2024-07-11 07:48: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*年），然后对其进行进一步的数学运算以获得月份等。

Are you trying to calculate the total days/months/years of an age? do you have a starting date? Or are you trying to dissect it (ex: 24 years, 1 month, 29 days)?

If you have a start date that you're working with, datediff will output the total days/months/years with the following commands:

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

with the respective outputs being (8827/290/24).

Now, if you wanted to do the dissection method, you'd have to subtract the number of years in days (days - 365*years), and then do further math on that to get the months, etc.

回复 原文
雨落星ぅ辰 2024-07-11 07:48:12

很老的问题，但我想分享我为计算年龄所做的事情

    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)

Quite Old question, but I want to share what I have done to calculate age

    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)
回复 原文
时光磨忆 2024-07-11 07:48:12

下面的 SQL 代码可以提供自 sysdate 以来的年数、月数和天数。
输入此格式的 input_birth_date 值 (dd_mon_yy)。 注意：年、月、年输入相同的值（出生日期）。 例如 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

Here is SQL code that gives you the number of years, months, and days since the sysdate.
Enter value for input_birth_date this format(dd_mon_yy). note: input same value(birth date) for years, months & days such as 01-mar-85

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
回复 原文
深爱成瘾 2024-07-11 07:48:12

试试这个...

SELECT CASE WHEN
 (DATEADD(year,DATEDIFF(year, @datestart  ,@dateend) , @datestart) > @dateend)
THEN DATEDIFF(year, @datestart  ,@dateend) -1
ELSE DATEDIFF(year, @datestart  ,@dateend)
END

基本上“DateDiff（年份...”，给你这个人今年的年龄，所以我刚刚添加了一个案例声明，如果他们今年还没有过生日，那么减去 1 年，否则返回该值。

Try this...

SELECT CASE WHEN
 (DATEADD(year,DATEDIFF(year, @datestart  ,@dateend) , @datestart) > @dateend)
THEN DATEDIFF(year, @datestart  ,@dateend) -1
ELSE DATEDIFF(year, @datestart  ,@dateend)
END

Basically the "DateDiff( year...", gives you the age the person will turn this year, so i have just add a case statement to say, if they have not had a birthday yet this year, then subtract 1 year, else return the value.

回复 原文
掩耳倾听 2024-07-11 07:48:12

我创建了一个函数calculateAge，它从外部获取参数dateOfBirth，然后计算以年、月和日为单位的年龄，最后以字符串格式返回。

CREATE FUNCTION calculateAge(dateOfBirth datetime) RETURNS varchar(40)
BEGIN
    set @currentdatetime = CURRENT_TIMESTAMP;
    set @years = TIMESTAMPDIFF(YEAR,dateOfBirth,@currentdatetime);
    set @months = TIMESTAMPDIFF(MONTH,dateOfBirth,@currentdatetime) - @years*12 ;
    set @dayOfBirth = EXTRACT(DAY FROM dateOfBirth);
    set @today = EXTRACT(DAY FROM @currentdatetime);
    set @days = 0;
    if (@today > @dayOfBirth) then
        set @days = @today - @dayOfBirth;
    else
        set @decreaseMonth = DATE_SUB(@currentdatetime, INTERVAL 1 MONTH);
        set @days = DATEDIFF(dateOfBirth, @decreaseMonth);
    end if;
    RETURN concat(concat( concat(@years , "years\n") , concat(@months , "months\n")), concat(@days , "days"));
END

I have created a function calculateAge that takes parameter dateOfBirth from outside and then it calculates the age in years, months and days and finally it returns in string format.

CREATE FUNCTION calculateAge(dateOfBirth datetime) RETURNS varchar(40)
BEGIN
    set @currentdatetime = CURRENT_TIMESTAMP;
    set @years = TIMESTAMPDIFF(YEAR,dateOfBirth,@currentdatetime);
    set @months = TIMESTAMPDIFF(MONTH,dateOfBirth,@currentdatetime) - @years*12 ;
    set @dayOfBirth = EXTRACT(DAY FROM dateOfBirth);
    set @today = EXTRACT(DAY FROM @currentdatetime);
    set @days = 0;
    if (@today > @dayOfBirth) then
        set @days = @today - @dayOfBirth;
    else
        set @decreaseMonth = DATE_SUB(@currentdatetime, INTERVAL 1 MONTH);
        set @days = DATEDIFF(dateOfBirth, @decreaseMonth);
    end if;
    RETURN concat(concat( concat(@years , "years\n") , concat(@months , "months\n")), concat(@days , "days"));
END
回复 原文
星 2024-07-11 07:48:12

还有另一种计算年龄的方法是

见下表

    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

There is another method for calculate age is

See below table

    FirstName       LastName    DOB
    sai             krishnan    1991-11-04
    Harish          S A         1998-10-11

For finding age,you can calculate through month

  Select datediff(MONTH,DOB,getdate())/12 as dates from [Organization].[Employee]

Result will be

firstname   dates
sai         27
Harish      20
回复 原文
对岸观火 2024-07-11 07:48:12

与函数是同一类东西。

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

The same sort of thing as a function.

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
回复 原文
在风中等你 2024-07-11 07:48:12

这是一个（稍微）简单的版本：

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

Here is a (slightly) simpler version:

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
回复 原文
骄兵必败 2024-07-11 07:48:12

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)

DateTime values in T-SQL are stored as floats. You can just subtract the dates from each other and you now have a new date that is the timespan between them.

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)
回复 原文
眼睛会笑 2024-07-11 07:48:12
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')

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')
回复 原文
雨轻弹 2024-07-11 07:48:12
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

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

Output

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
回复 原文
随遇而安 2024-07-11 07:48:12

我使用我修改过的这个函数（天数部分）来自@Dane的回答： https://stackoverflow.com/a/57720/2097023< /a>

CREATE 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

它工作得很好。

I use this Function I modified (the Days part) From @Dane answer: https://stackoverflow.com/a/57720/2097023

CREATE 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

It works pretty well.

回复 原文
絕版丫頭 2024-07-11 07:48:12

我已经多次看到这个问题，结果输出年、月、日，但从未输出数字/小数结果。 （至少没有一个不正确舍入的）。
我欢迎对此功能的反馈。 可能还不需要一点调整。

-- 函数的输入是两个日期。
-- 输出是两个日期之间的年数，采用 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

`

I've seen the question several times with results outputting Years, Month, Days but never a numeric / decimal result. (At least not one that doesn't round incorrectly).
I welcome feedback on this function. Might not still need a little adjusting.

-- Input to the function is two dates.
-- Output is the numeric number of years between the two dates in Decimal(7,4) format.
-- Output is always always a possitive number.

-- NOTE:Output does not handle if difference is greater than 999.9999

-- Logic is based on three steps.
-- 1) Is the difference less than 1 year (0.5000, 0.3333, 0.6667, ect.)
-- 2) Is the difference exactly a whole number of years (1,2,3, ect.)

-- 3) (Else)...The difference is years and some number of days. (1.5000, 2.3333, 7.6667, ect.)

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

`

回复 原文
皇甫轩 2024-07-11 07:48:12
select DOB as Birthdate,
       YEAR(GETDATE()) as ThisYear, 
       YEAR(getdate()) - EAR(date1) as Age   
from TableName

select DOB as Birthdate,
       YEAR(GETDATE()) as ThisYear, 
       YEAR(getdate()) - EAR(date1) as Age   
from TableName
回复 原文
满栀 2024-07-11 07:48:12
SELECT DOB AS Birthdate ,
       YEAR(GETDATE()) AS ThisYear,
       YEAR(getdate()) - YEAR(DOB) AS Age
FROM tableprincejain

SELECT DOB AS Birthdate ,
       YEAR(GETDATE()) AS ThisYear,
       YEAR(getdate()) - YEAR(DOB) AS Age
FROM tableprincejain
回复 原文
赢得她心 2024-07-11 07:48:12
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

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
回复 原文
耳根太软 2024-07-11 07:48:12

以下是如何计算给定出生日期和当前日期的年龄。

查询：

DECLARE @Date_Of_Birth date = '19991220' -- set Birthday
Select Concat( 
  DATEDIFF(DAY, @Date_Of_Birth , GETDATE()) % 365 % 30, ' Days ' , 
  DATEDIFF(MONTH, @Date_Of_Birth, GETDATE()) % 12 , ' Months ', 
  DATEDIFF(MONTH, @Date_Of_Birth, GETDATE())/12, ' Years'
) As Age

输出：
年龄
27天5个月23年

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

Query:

DECLARE @Date_Of_Birth date = '19991220' -- set Birthday
Select Concat( 
  DATEDIFF(DAY, @Date_Of_Birth , GETDATE()) % 365 % 30, ' Days ' , 
  DATEDIFF(MONTH, @Date_Of_Birth, GETDATE()) % 12 , ' Months ', 
  DATEDIFF(MONTH, @Date_Of_Birth, GETDATE())/12, ' Years'
) As Age

Output:
Age
27 Days 5 Months 23 Years

回复 原文
浅忆 2024-07-11 07:48:12
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]

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]
回复 原文
逐鹿 2024-07-11 07:48:12

通过 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

按案例陈述的天数的简短版本：

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 日期格式

Implemented by arithmetic with ISO formatted date.

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

Test Cases

The approach of days is different from the accepted answer, the differences shown in the comments below:

       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

An short version of Days by case statement:

set @days = CASE WHEN day(@now) >= day(@dob) THEN day(@now) - day(@dob)
                 ELSE @days_in_birth_month - day(@dob) + day(@now) END

If you want the age of years and months only, it could be simpler

set @years = ( @now_i/100 - @dob_i/100)/100
set @months =(12 + month(@now) - month(@dob))%12 
select @years,@months -- 1, 0

NOTE: A very useful link of SQL Server Date Formats

回复 原文
讽刺将军 2024-07-11 07:48:12

已经给出了很多解决方案，但我相信这个解决方案既易于理解又可靠，因为它也可以处理闰年：

case when datepart(dayofyear, @birth) <= datepart(dayofyear, getdate())
then datepart(year, getdate()) - datepart(year, @birth)
else datepart(year, getdate()) - datepart(year, @birth) - 1
end

这个想法是简单地计算两年（出生和现在）之间的年份差异，如果当年尚未达到周年纪念日，则减去 1。

Plenty of solutions have been given already, but I beleive this one to be both easy to understand and reliable, as it will handle leap years as well :

case when datepart(dayofyear, @birth) <= datepart(dayofyear, getdate())
then datepart(year, getdate()) - datepart(year, @birth)
else datepart(year, getdate()) - datepart(year, @birth) - 1
end

The idea is to simply compute the difference in years between the two years (birth and now), and substract 1 if the anniversary has not been reached for the current year.

回复 原文
听，心雨的声音 2024-07-11 07:48:12
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

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
回复 原文
悲凉≈ 2024-07-11 07:48:12

以下是我根据出生日期和当前日期计算年龄的方法。

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

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

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
回复 原文
待天淡蓝洁白时 2024-07-11 07:48:12

有一种简单的方法，基于两天之间的小时数，但结束日期被截断。

SELECT CAST(DATEDIFF(hour,Birthdate,CAST(GETDATE() as Date))/8766.0 as INT) AS Age FROM <YourTable>

事实证明，这一方法极其准确和可靠。 如果没有 GETDATE() 上的内部 CAST，它可能会在午夜前几个小时翻转生日，但是，有了 CAST，它就会在恰好午夜时改变年龄。

There is an easy way, based on the hours between the two days BUT with the end date truncated.

SELECT CAST(DATEDIFF(hour,Birthdate,CAST(GETDATE() as Date))/8766.0 as INT) AS Age FROM <YourTable>

This one has proven to be extremely accurate and reliable. If it weren't for the inner CAST on the GETDATE() it might flip the birthday a few hours before midnight but, with the CAST, it is dead on with the age changing over at exactly midnight.

回复 原文
蓝眼泪 2024-07-11 07:48:12

对于那些想要在表中创建计算列来存储年龄的人：

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

For the ones that want to create a calculated column in a table to store the age:

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
回复 原文
~没有更多了~

关于作者

单挑你×的.吻

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

qq_FjTq5B

文章 0 评论 0

18273202778

文章 0 评论 0

WordPress小学生

文章 0 评论 0

〃温暖了心ぐ

文章 0 评论 0

迷乱花海

文章 0 评论 0

niuniu

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文