我有一个表，上面列出了人们的出生日期(目前是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
当前回答
这个怎么样:
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
其他回答
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
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
)
来源:http://beginsql.wordpress.com/2012/04/26/how-to-calculate-age-in-sql-server/
你应该使用 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 这个年龄在全年
所以上面的很多解决方案都是错误的DateDiff(yy，@Dob， @PassedDate)不会考虑两个日期的月和日。同样，只有在正确排序的情况下，省道部件才能进行比较。
下面的代码非常简单:
create function [dbo].[AgeAtDate](
@DOB datetime,
@PassedDate datetime
)
returns int
with SCHEMABINDING
as
begin
declare @iMonthDayDob int
declare @iMonthDayPassedDate int
select @iMonthDayDob = CAST(datepart (mm,@DOB) * 100 + datepart (dd,@DOB) AS int)
select @iMonthDayPassedDate = CAST(datepart (mm,@PassedDate) * 100 + datepart (dd,@PassedDate) AS int)
return DateDiff(yy,@DOB, @PassedDate)
- CASE WHEN @iMonthDayDob <= @iMonthDayPassedDate
THEN 0
ELSE 1
END
End
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
