Ik heb een tabel met mensen samen met hun geboortedatum (momenteel een NVARCHAR (25))
Hoe kan ik dat omzetten in een datum en vervolgens hun leeftijd bereken in jaren?
Mijn gegevens zoeken als volgt
ID Name DOB
1 John 1992-01-09 00:00:00
2 Sally 1959-05-20 00:00:00
Ik zou graag zien:
ID Name AGE DOB
1 John 17 1992-01-09 00:00:00
2 Sally 50 1959-05-20 00:00:00
1, Autoriteit 100%
Er zijn problemen met schrikkeljaar / dagen en de volgende methode, zie hieronder de update:
Probeer dit:
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 AgeYearsIntTruncUitgang:
AgeYearsDecimal AgeYearsIntRound AgeYearsIntTrunc --------------------------------------- ---------------- ---------------- 17.767054 18 17 (1 row(s) affected)
update Hier zijn enkele nauwkeurige methoden:
Beste methode voor jaren in 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
U kunt de bovenstaande 10000wijzigen op 10000.0en decimalen krijgen, maar het is niet zo nauwkeurig als de onderstaande methode.
beste methode voor jaren in decimaal
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
Antwoord 2, autoriteit 55%
Ik moet deze weggooien. Als u de datum converteertin de stijl 112 (jjjjmmdd) naar een getal je kunt een berekening als deze gebruiken…
(jjjjMMdd – jjjjMMdd) / 10000 = verschil in hele jaren
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
uitvoer
20091015 19800420 290595 29
Antwoord 3, autoriteit 16%
Ik gebruik deze zoekopdracht al bijna 10 jaar in onze productiecode:
SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age
Antwoord 4, autoriteit 12%
Zo veel van de bovenstaande oplossingen zijn fout DateDiff(yy,@Dob, @PassedDate) houdt geen rekening met de maand en de dag van beide datums. Ook het nemen van de dartonderdelen en vergelijken werkt alleen als ze goed zijn besteld.
DE VOLGENDE CODE WERKT EN IS HEEL EENVOUDIG:
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
Antwoord 5, autoriteit 9%
Je moet rekening houden met de manier waarop het datediff-commando wordt afgerond.
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>
Die ik hierheb aangepast.
Merk op dat 28 februari wordt beschouwd als de verjaardag van een schrikkeljaar voor niet-schrikkeljaren, b.v. een persoon geboren op 29 februari 2020 wordt beschouwd als 1 jaar oud op 28 februari 2021 in plaats van 1 maart 2021.
6, Autoriteit 2%
Ik geloof dat dit vergelijkbaar is met andere die hier is geplaatst …. maar deze oplossing werkte voor de schrikkeljaar voorbeelden 02/29/1976 tot 03/01/2011 en werkte ook voor het eerste jaar voor het eerste jaar. 07/04/2011 tot 07/03/2012 Wie de laatste is geplaatst over schrikkeljaarsoplossing van het eerste jaar niet voor het geval van het eerste jaar.
SELECT FLOOR(DATEDIFF(DAY, @date1 , @date2) / 365.25)
hier .
7, Autoriteit 2%
Omdat er geen eenvoudig antwoord is dat altijd de juiste leeftijd geeft, is hier wat ik bedacht.
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
Dit krijgt het jaarverschil tussen de geboortedatum en de huidige datum. Dan trekt het een jaar af als de geboortedatum nog niet is doorgegeven.
Nauwkeurig de hele tijd – ongeacht de schrikkeljaren of hoe dicht bij de geboortedatum.
het beste van alles – geen functie.
8
Hoe zit het met:
DECLARE @DOB datetime
SET @DOB='19851125'
SELECT Datepart(yy,convert(date,GETDATE())-@DOB)-1900
Zou dat niet al die afrondings-, afkap- en verrekeningsproblemen voorkomen?
Antwoord 9
Ik heb hier veel over nagedacht en gezocht en ik heb 3 oplossingen die
- leeftijd correct berekenen
- zijn kort (meestal)
- zijn (meestal) heel begrijpelijk.
Hier zijn testwaarden:
DECLARE @NOW DATETIME = '2013-07-04 23:59:59'
DECLARE @DOB DATETIME = '1986-07-05'
Oplossing 1:ik vond deze aanpak in één js-bibliotheek. Het is mijn favoriet.
DATEDIFF(YY, @DOB, @NOW) -
CASE WHEN DATEADD(YY, DATEDIFF(YY, @DOB, @NOW), @DOB) > @NOW THEN 1 ELSE 0 END
Het is eigenlijk het optellen van het verschil in jaren bij de DOB en als het groter is dan de huidige datum, wordt er één jaar afgetrokken. Simpel toch? Het enige is dat het verschil in jaren hier wordt gedupliceerd.
Maar als je het niet inline hoeft te gebruiken, kun je het als volgt schrijven:
DECLARE @AGE INT = DATEDIFF(YY, @DOB, @NOW)
IF DATEADD(YY, @AGE, @DOB) > @NOW
SET @AGE = @AGE - 1
Oplossing 2:Deze heb ik oorspronkelijk gekopieerd van @bacon-bits. Het is het gemakkelijkst te begrijpen, maar een beetje lang.
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
Het is eigenlijk het berekenen van leeftijd zoals wij mensen doen.
Oplossing 3: My Friend Refactured It Hierin:
DATEDIFF(YY, @DOB, @NOW) -
CEILING(0.5 * SIGN((MONTH(@DOB) - MONTH(@NOW)) * 50 + DAY(@DOB) - DAY(@NOW)))
Deze is de kortste, maar het is het moeilijkst om te begrijpen. 50is slechts een gewicht, dus het dagverschil is alleen belangrijk wanneer maanden hetzelfde is. SIGNFunctie is voor het transformeren van elke waarde die het krijgt op -1, 0 of 1. CEILING(0.5 *is hetzelfde als Math.max(0, value)Maar er is niet zoiets in SQL.
10
Controleer gewoon of het onderstaande antwoord haalbaar is.
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
)
11
SELECT ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME, DOB),GETDATE()) AS AGE,
DOB
FROM MyTable
12
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
)
Bron: http: / /beginsql.wordpress.com/2012/04/26/How-to-calculate-age-in-SQL-SERVER/
13
Na VEEL methoden geprobeerd te hebben, werkt dit 100% van de tijd met behulp van de moderne MS SQL FORMAT functie in plaats van converteren naar stijl 112. Beide zouden werken, maar dit is de minste code.
Kan iemand een datumcombinatie vinden die niet werkt? Ik denk niet dat die er is 🙂
--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
Antwoord 14
Probeer dit
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'
Antwoord 15
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
Antwoord 16
select floor((datediff(day,0,@today) - datediff(day,0,@birthdate)) / 365.2425) as age
Er zijn hier veel 365,25 antwoorden. Onthoud hoe schrikkeljaren worden gedefinieerd:
- Elke vier jaar
- behalveom de 100 jaar
- behalveom de 400 jaar
- behalveom de 100 jaar
Antwoord 17
Wat dacht je hiervan:
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
Antwoord 18
Probeer deze oplossing:
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
Antwoord 19
Dit zal de problemen met de verjaardag en afronding correct afhandelen:
DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)
Antwoord 20
Ed Harper’s oplossing is de eenvoudigste die ik heb gevonden en geeft nooit het verkeerde antwoord als de maand en de dag van de twee datums 1 of minder dagen uit elkaar liggen. Ik heb een kleine wijziging aangebracht om negatieve leeftijden te verwerken.
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
Antwoord 21
Het antwoord dat als correct is gemarkeerd, is nauwkeuriger, maar mislukt in het volgende scenario – waar Geboortejaar is Schrikkeljaar en dag vallen na de maand februari
declare @ReportStartDate datetime = CONVERT(datetime, '1/1/2014'),
@DateofBirth datetime = CONVERT(datetime, '2/29/1948')
FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8766)
OF
FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82) -- Divisor is more accurate than 8766
— De volgende oplossing geeft me nauwkeurigere resultaten.
FLOOR(DATEDIFF(YEAR,@DateofBirth,@ReportStartDate) - (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,@DateofBirth,@ReportStartDate),@DateofBirth) > @ReportStartDate THEN 1 ELSE 0 END ))
Het werkte in bijna alle scenario’s, rekening houdend met schrikkeljaar, datum als 29 februari, enz.
Corrigeer me als deze formule een maas in de wet bevat.
Antwoord 22
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
Antwoord 23
Hier is hoe ik de leeftijd bereken op basis van een geboortedatum en huidige datum.
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
Antwoord 24
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
Antwoord 25
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
26
Hoe zit het met een oplossing met alleen datumfuncties, niet wiskunde, geen zorgen over schrikkeljaar
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
27
Hier is een techniek die rond de randgevallen lijkt te werken: het einde van het jaar en sprong februari.
Het probleem met datediff(year,…,…)is dat het alleen het verschil tussen de jaren vindt, en niet de werkelijke data, die naar mijn mening een beetje naïef is. Dit is alleen betrouwbaar als de geboortedatum 1 januari is.
De oplossing is om de geboortedatum van de eerste van januari en de vraagdatum met hetzelfde bedrag te vervormen.
Vanwege het probleem van de schrikkeljaar met behulp van de dag van het jaar is ook onbetrouwbaar, dus ik gebruik de maand en dag om de Time Warp te doen:
CREATE FUNCTION age(@then AS date,@now AS date) RETURNS int AS
BEGIN
DECLARE @month INT = month(@then)-1;
DECLARE @day INT = day(@then)-1;
SET @then=dateadd(month,-@month,@then);
SET @then=dateadd(day,-@day,@then);
SET @now=dateadd(month,-@month,@now;)
SET @now=dateadd(day,-@day,@now);
RETURN datediff(year,@then,@now);
END;
U kunt deze compacter te schrijven, natuurlijk. U kunt zelfs schrijf het in één lijn, als dat uw idee van een goede tijd:
CREATE FUNCTION age(@then AS date,@now AS date) RETURNS int AS
BEGIN
RETURN datediff(
year,
dateadd(day,-day(@then)+1,dateadd(month,-month(@then)+1,@then)),
dateadd(day,-day(@then)+1,dateadd(month,-month(@then)+1,@now))
);
END;
maar dat is alleen te pronken, en het is minder efficiënt als het nodig heeft om de offsets herberekenen.
28
select datediff(day,'1991-03-16',getdate()) \\for days,get date refers today date
select datediff(year,'1991-03-16',getdate()) \\for years
select datediff(month,'1991-03-16',getdate()) \\for month
29
We gebruikten zoiets als hier, maar dan is het nemen van de gemiddelde leeftijd:
ROUND(avg(CONVERT(int,DATEDIFF(hour,DOB,GETDATE())/8766.0)),0) AS AverageAge
Merk de RONDE buiten dan erbinnen. Dit zal zorgen voor de AVG om meer accurate en ronden we slechts één keer. Waardoor het sneller ook.