在T-SQL(SQL Server 2000)中计算某人的年，月和日的年龄的最佳方法是什么？

datediff函数不能很好地处理年份的界限，另外，将月份和日期分开将是一个负担。我知道我可以在客户端相对容易地做到这一点，但是我想在我的存储过程中完成它。

解决方案

回答

我们是否要计算年龄的总天数/月数/年数？我们有开始日期吗？还是要剖析(例如：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天*年)，然后对此做进一步的数学运算，以获取月份等。

回答

这是一些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

回答

这是一个(略)简单的版本：

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

回答

与功能相同的事物。

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

回答

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