我有一个表，上面列出了人们的出生日期(目前是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 @birthday as datetime
set @birthday = '2000-01-01'
declare @today as datetime
set @today = GetDate()
select
case when ( substring(convert(varchar, @today, 112), 5,4) >= substring(convert(varchar, @birthday, 112), 5,4) ) then
(datepart(year,@today) - datepart(year,@birthday))
else
(datepart(year,@today) - datepart(year,@birthday)) - 1
end
其他回答
SELECT ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME, DOB),GETDATE()) AS AGE,
DOB
FROM MyTable
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 @BirthDate datetime
declare @ToDate datetime
set @BirthDate = '1/3/1990'
set @ToDate = '1/2/2008'
select @BirthDate [Date of Birth], @ToDate [ToDate],(case when (DatePart(mm,@ToDate) < Datepart(mm,@BirthDate))
OR (DatePart(m,@ToDate) = Datepart(m,@BirthDate) AND DatePart(dd,@ToDate) < Datepart(dd,@BirthDate))
then (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate) - 1)
else (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate))end) Age
简单明了
SELECT (YEAR(CURRENT_TIMESTAMP) - YEAR(birthday)) as age FROM db_deirvlon_monyo_users
如果你找到你的年龄。 Select (months_between(sysdate,dob)/12) from table_name 如果你找到你的大致年龄。 从table_name中选择round(months_between(sysdate,dob)/12);在这里输入代码 dob是列名。用于当前日期的Sysdate。Months_between用于查找dob到当前日期的总月份
