생년월일 및 getDate ()를 기준으로 연령 (년)을 계산하는 방법
생년월일 (현재 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(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산출:
AgeYearsDecimal AgeYearsIntRound AgeYearsIntTrunc --------------------------------------- ---------------- ---------------- 17.767054 18 17 (1 row(s) affected)
여기에 업데이트 더 정확한 방법이 있습니다 :
INT 년 동안 가장 좋은 방법
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
이걸 버려야 해. 당신이 경우 변환 숫자로 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
거의 10 년 동안 프로덕션 코드에서이 쿼리를 사용했습니다.
SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age
위의 많은 솔루션이 잘못되었습니다 .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
datediff 명령이 반올림되는 방식을 고려해야합니다.
SELECT CASE WHEN dateadd(year, datediff (year, DOB, getdate()), DOB) > getdate()
THEN datediff(year, DOB, getdate()) - 1
ELSE datediff(year, DOB, getdate())
END as Age
FROM <table>
편집 :이 답변이 잘못되었습니다. 나는 여기 dayofyear에 추가 유혹 과 함께 사용하려는 유혹을 가진 사람들에게 경고로 남겨 둡니다 .
나처럼 분수 일로 나누거나 반올림 / 윤년 오류를 위험으로 나누고 싶지 않다면 https://stackoverflow.com/a/1572257/489865 위의 게시물에 @Bacon Bits 의견을 박수로 보냅니다 .
우리가 인간 연령에 대해 이야기하고 있다면, 인간이 연령을 계산하는 방식으로 계산해야합니다. 지구가 얼마나 빨리 움직이는 지, 달력과 관련된 모든 것과는 아무런 관련이 없습니다. 생년월일과 동일한 월과 일이 경과 할 때마다 나이가 1 씩 증가합니다. 이는 다음과 같이 인간이 "나이"라고 말할 때의 의미를 반영하기 때문에 가장 정확합니다.
그는 다음을 제공합니다.
DATEDIFF(yy, @date, GETDATE()) -
CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE()))
THEN 1 ELSE 0 END
여기에는 월 & 일을 비교하는 몇 가지 제안이 있습니다 (일부는 잘못하여 OR여기를 올바르게 허용하지 못합니다!). 그러나 아무도 제공하지 않았습니다 dayofyear. 그렇게 간단하고 훨씬 짧은 것 같습니다. 나는 제안한다 :
DATEDIFF(year, @date, GETDATE()) -
CASE WHEN DATEPART(dayofyear, @date) > DATEPART(dayofyear, GETDATE()) THEN 1 ELSE 0 END
[참고 : SQL BOL / MSDN의 어디에도 DATEPART(dayofyear, ...)실제로 반환되는 문서는 없습니다! 1-366 범위의 숫자 인 것으로 알고 있습니다. 가장 중요한 것은 & 로 로케일에 따라 바뀌지 않는다는 것 입니다.]DATEPART(weekday, ...)SET DATEFIRST
편집 : 왜 dayofyear잘못 : 출산 / 날짜가 아닌 윤년 2 월 이후 시작하면 현재 / 종료 날짜가 윤년 인 경우 사용자로서 @AeroX이 예는, 나이가 일일 초 증가, 댓글을 달았습니다 '2015-05-26', '2016-05-25'을 제공합니다 나이가 여전히 0이어야하는 1 세 dayofyear. 다른 연도를 비교하는 것은 분명히 위험합니다. 따라서를 사용 MONTH()하고 DAY()결국 필요합니다.
항상 정확한 나이를 제공하는 간단한 대답이 없기 때문에 여기에 내가 생각해 낸 것이 있습니다.
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
이것은 생년월일과 현재 날짜 사이의 연도 차이를 가져옵니다. 생년월일이 아직 지나지 않으면 1 년을 뺍니다.
윤년이나 생년월일에 관계없이 항상 정확합니다.
무엇보다도-기능이 없습니다.
SELECT ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME, DOB),GETDATE()) AS AGE,
DOB
FROM MyTable
이건 어떤가요:
DECLARE @DOB datetime
SET @DOB='19851125'
SELECT Datepart(yy,convert(date,GETDATE())-@DOB)-1900
반올림, 잘림 및 설정 문제를 피하지 않겠습니까?
나는 이것이 여기에 게시 된 다른 것과 유사하다고 생각합니다 ....이 솔루션은 윤년 예제 02/29/1976에서 03/01/2011에 효과가 있었고 첫해의 경우에도 효과가있었습니다. 07/04 윤년 솔루션에 대해 마지막으로 게시 한 첫 번째 사례는 첫해 유스 케이스에서 작동하지 않았습니다.
SELECT FLOOR(DATEDIFF(DAY, @date1 , @date2) / 365.25)
찾을 여기 .
아래 답변이 가능한지 확인하십시오.
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
)
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/
나는 이것에 대해 많은 생각과 검색을했으며 3 가지 해결책이 있습니다.
- 나이를 정확하게 계산
- 짧다 (주로)
- (대부분) 매우 이해할 수 있습니다.
테스트 값은 다음과 같습니다.
DECLARE @NOW DATETIME = '2013-07-04 23:59:59'
DECLARE @DOB DATETIME = '1986-07-05'
솔루션 1 : 하나의 js 라이브러리 에서이 방법을 찾았습니다. 내가 가장 좋아하는 것입니다.
DATEDIFF(YY, @DOB, @NOW) -
CASE WHEN DATEADD(YY, DATEDIFF(YY, @DOB, @NOW), @DOB) > @NOW THEN 1 ELSE 0 END
실제로 DOB에 연도 차이를 추가하고 있으며 현재 날짜보다 크면 1 년을 뺍니다. 간단 하죠? 유일한 것은 여기에 연도 차이가 복제된다는 것입니다.
그러나 인라인을 사용할 필요가 없으면 다음과 같이 작성할 수 있습니다.
DECLARE @AGE INT = DATEDIFF(YY, @DOB, @NOW)
IF DATEADD(YY, @AGE, @DOB) > @NOW
SET @AGE = @AGE - 1
해결책 2 : 이것은 원래 @ bacon-bits에서 복사 한 것입니다. 이해하기가 가장 쉽지만 조금 길다.
DATEDIFF(YY, @DOB, @NOW) -
CASE WHEN MONTH(@DOB) > MONTH(@NOW)
OR MONTH(@DOB) = MONTH(@NOW) AND DAY(@DOB) > DAY(@NOW)
THEN 1 ELSE 0 END
기본적으로 인간이 나이를 계산합니다.
해결책 3 : 친구가 이것을 다음과 같이 리팩토링했습니다.
DATEDIFF(YY, @DOB, @NOW) -
CEILING(0.5 * SIGN((MONTH(@DOB) - MONTH(@NOW)) * 50 + DAY(@DOB) - DAY(@NOW)))
이것은 가장 짧지 만 이해하기가 가장 어렵습니다. 50월이 같을 때만 차이가 중요하므로 하루의 차이 만 중요합니다. SIGN함수는 -1, 0 또는 1로 가져 가치를 어떤 변환하기위한 것입니다 CEILING(0.5 *과 동일 Math.max(0, value)하지만, SQL에서 그런 일이 없다.
CASE WHEN datepart(MM, getdate()) < datepart(MM, BIRTHDATE) THEN ((datepart(YYYY, getdate()) - datepart(YYYY, BIRTH_DATE)) -1 )
ELSE
CASE WHEN datepart(MM, getdate()) = datepart(MM, BIRTHDATE)
THEN
CASE WHEN datepart(DD, getdate()) < datepart(DD, BIRTHDATE) THEN ((datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE)) -1 )
ELSE (datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE))
END
ELSE (datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE)) END
END
select floor((datediff(day,0,@today) - datediff(day,0,@birthdate)) / 365.2425) as age
여기에 365.25 답변이 많이 있습니다. 윤년 정의 방법을 기억하십시오 :
- 4 년마다
- 100 년마다를
제외하고
- 400 년을 제외하고
- 100 년마다를
제외하고
이건 어때요:
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 @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '08/16/84'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
SELECT Convert(Varchar(Max),@years)+' Years '+ Convert(Varchar(max),@months) + ' Months '+Convert(Varchar(Max), @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
생일 및 반올림 문제를 올바르게 처리합니다.
DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)
Ed Harper의 솔루션은 내가 찾은 가장 간단한 방법으로 두 날짜의 월과 일이 1 일 이하일 때 오답을 반환하지 않습니다. 나는 부정적인 연령을 처리하기 위해 약간 수정했습니다.
DECLARE @D1 AS DATETIME, @D2 AS DATETIME
SET @D2 = '2012-03-01 10:00:02'
SET @D1 = '2013-03-01 10:00:01'
SELECT
DATEDIFF(YEAR, @D1,@D2)
+
CASE
WHEN @D1<@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) > @D2
THEN - 1
WHEN @D1>@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) < @D2
THEN 1
ELSE 0
END AS AGE
The answer marked as correct is nearer to accuracy but, it fails in following scenario - where Year of birth is Leap year and day are after February month
declare @ReportStartDate datetime = CONVERT(datetime, '1/1/2014'),
@DateofBirth datetime = CONVERT(datetime, '2/29/1948')
FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8766)
OR
FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82) -- Divisor is more accurate than 8766
-- Following solution is giving me more accurate results.
FLOOR(DATEDIFF(YEAR,@DateofBirth,@ReportStartDate) - (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,@DateofBirth,@ReportStartDate),@DateofBirth) > @ReportStartDate THEN 1 ELSE 0 END ))
It worked in almost all scenarios, considering leap year, date as 29 feb, etc.
Please correct me if this formula have any loophole.
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
Here is how i calculate age given a birth date and current date.
select case
when cast(getdate() as date) = cast(dateadd(year, (datediff(year, '1996-09-09', getdate())), '1996-09-09') as date)
then dateDiff(yyyy,'1996-09-09',dateadd(year, 0, getdate()))
else dateDiff(yyyy,'1996-09-09',dateadd(year, -1, getdate()))
end as MemberAge
go
CREATE function dbo.AgeAtDate(
@DOB datetime,
@CompareDate datetime
)
returns INT
as
begin
return CASE WHEN @DOB is null
THEN
null
ELSE
DateDiff(yy,@DOB, @CompareDate)
- CASE WHEN datepart(mm,@CompareDate) > datepart(mm,@DOB) OR (datepart(mm,@CompareDate) = datepart(mm,@DOB) AND datepart(dd,@CompareDate) >= datepart(dd,@DOB))
THEN 0
ELSE 1
END
END
End
GO
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
What about a solution with only date functions, not math, not worries about leap year
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
After trying MANY methods, this works 100% of the time using the modern MS SQL FORMAT function instead of convert to style 112. Either would work but this is the least code.
Can anyone find a date combination which does not work? I don't think there is one :)
--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
We used something like here, but then taking the average age:
ROUND(avg(CONVERT(int,DATEDIFF(hour,DOB,GETDATE())/8766.0)),0) AS AverageAge
Notice, the ROUND is outside rather than inside. This will allow for the AVG to be more accurate and we ROUND only once. Making it faster too.
select DATEDIFF(yy,@DATE,GETDATE()) -
case when DATEPART(mm,GETDATE())*100+DATEPART(dd,GETDATE())>=
DATEPART(mm,@DATE)*100+DATEPART(dd,@DATE) THEN 0
ELSE 1 END
SELECT CAST(DATEDIFF(dy, @DOB, GETDATE()+1)/365.25 AS int)
'programing tip' 카테고리의 다른 글
| 객체를 포함하는 배열을 반복하고 속성에 액세스하는 방법 (0) | 2020.06.05 |
|---|---|
| Docker에 볼륨을 추가하지만 하위 폴더는 제외 (0) | 2020.06.05 |
| jQuery로 입력 값을 설정 한 후 각도 모델 업데이트 (0) | 2020.06.05 |
| 기본값으로 bash에서 변수 읽기 (0) | 2020.06.05 |
| 크롬이 아닌 사파리 전용 CSS 해킹이 있습니까? (0) | 2020.06.05 |