我有一个表，上面列出了人们的出生日期(目前是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='1992-01-09 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)
其他回答
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 DATE = '09/06/1979'
SELECT
(
YEAR(GETDATE()) - YEAR(@BirthDate) -
CASE WHEN (MONTH(GETDATE()) * 100) + DATEPART(dd, GETDATE()) >
(MONTH(@BirthDate) * 100) + DATEPART(dd, @BirthDate)
THEN 1
ELSE 0
END
)
所以上面的很多解决方案都是错误的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
一个只有日期函数的解决方案怎么样，不需要数学，不用担心闰年
CREATE FUNCTION dbo.getAge(@dt datetime)
RETURNS int
AS
BEGIN
RETURN
DATEDIFF(yy, @dt, getdate())
- CASE
WHEN
MONTH(@dt) > MONTH(GETDATE()) OR
(MONTH(@dt) = MONTH(GETDATE()) AND DAY(@dt) > DAY(GETDATE()))
THEN 1
ELSE 0
END
END
在尝试了许多方法后，这是100%的工作时间使用现代MS SQL格式函数，而不是转换为风格112。两者都可以，但这是最少的代码。
谁能找到一个日期组合不工作?我不认为有一个:)
--Set parameters, or choose from table.column instead:
DECLARE @DOB DATE = '2000/02/29' -- If @DOB is a leap day...
,@ToDate DATE = '2018/03/01' --...there birthday in this calculation will be
--0+ part tells SQL to calc the char(8) as numbers:
SELECT [Age] = (0+ FORMAT(@ToDate,'yyyyMMdd') - FORMAT(@DOB,'yyyyMMdd') ) /10000
