我有一个表，上面列出了人们的出生日期(目前是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
当前回答
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/
其他回答
是什么:
DECLARE @DOB datetime
SET @DOB='19851125'
SELECT Datepart(yy,convert(date,GETDATE())-@DOB)-1900
这难道不会避免所有的舍入、截断和抵消问题吗?
你应该使用 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 这个年龄在全年
我得把这个扔出去。如果您使用112样式(yyyymmdd)将日期转换为一个数字，您可以使用这样的计算…
(yyyyMMdd - yyyyMMdd) / 10000 =全年差值
declare @as_of datetime, @bday datetime;
select @as_of = '2009/10/15', @bday = '1980/4/20'
select
Convert(Char(8),@as_of,112),
Convert(Char(8),@bday,112),
0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112),
(0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112)) / 10000
输出
20091015 19800420 290595 29
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 @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
