我有一个表,上面列出了人们的出生日期(目前是nvarchar(25))

我如何将其转换为日期,然后以年为单位计算他们的年龄?

我的数据如下所示

ID    Name   DOB
1     John   1992-01-09 00:00:00
2     Sally  1959-05-20 00:00:00

我希望看到:

ID    Name   AGE  DOB
1     John   17   1992-01-09 00:00:00
2     Sally  50   1959-05-20 00:00:00

当前回答

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

其他回答

你应该使用 select FLOOR(DATEDIFF(CURDATE(),DATE(DOB))/365.25) from table_name; 这里CURDATE()使用当前日期,您可以以'yyyy-mm-dd'格式给出自己的日期 DATE(DOB)从DATETIME格式的列中提取yyyy-mm-dd年 这里DOB是你的列名(但你应该改变表修改数据类型为DATETIME在你的情况下是nvarchar) 注意: 此查询用于mysql 这个年龄在全年

因为没有一个简单的答案总是能给出正确的年龄,下面是我想到的。

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 

这将获得出生日期和当前日期之间的年差。如果出生日期还没过,就减去一年。

任何时候都是准确的——不管闰年或离生日有多近。

最棒的是——没有功能。

闰年/日和以下方法有问题,请参阅下面的更新:

try this: DECLARE @dob datetime SET @dob='1992-01-09 00:00:00' SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc OUTPUT: AgeYearsDecimal AgeYearsIntRound AgeYearsIntTrunc --------------------------------------- ---------------- ---------------- 17.767054 18 17 (1 row(s) affected)

以下是一些更准确的方法:

多年来最好的方法

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1989-05-06', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1990-05-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05'  --results in 10

SELECT
    (CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears

您可以将上面的10000更改为10000.0并获得小数,但它不会像下面的方法那样准确。

用十进制表示年份的最佳方法

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in  9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in  9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973

SELECT 1.0* DateDiff(yy,@Dob,@Now) 
    +CASE 
         WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN  --birthday has happened for the @now year, so add some portion onto the year difference
           (  1.0   --force automatic conversions from int to decimal
              * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
              / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
           )
         ELSE  --birthday has not been reached for the last year, so remove some portion of the year difference
           -1 --remove this fractional difference onto the age
           * (  -1.0   --force automatic conversions from int to decimal
                * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
                / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
             )
     END AS AgeYearsDecimal
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

这将正确地处理生日和舍入的问题:

DECLARE @dob  datetime
SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)