Recently, while filling some form needed to provide my age in years. Thought of calculating it in sql server and as usual used the DATEDIFF function like below, but it gave me wrong answer:
Script which gives Wrong Answer:
DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
GETDATE() 'Current Date',
DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'
Incorrect Result:
Date of Birth Current Date Age in Years ------------- ----------------------- ------------ 1980-12-29 2012-04-26 09:46:21.833 32 (1 row(s) affected)
Answer returned by the above query is 32 years which is 1 year more than the actual age. Reason for this mistake is, we are taking into consideration just the year part and not the day and month. So, we will get 1 year extra if the birth day has not yet crossed in the current year. So it means the above query will return correct age in years if the birthday has already passed in the current year.
To solve this issue we need to subtract 1 year if the birth date has not yet crossed in the current year:
Script which gives Correct Answer:
DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
DATEDIFF(YEAR,@DOB,GETDATE())
-
(CASE
WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
> GETDATE() THEN 1
ELSE 0 END)
'Age in Years'
Correct Result:
Date of Birth Current Date Age in Years ------------- ----------------------- ------------ 1980-12-29 2012-04-26 10:23:05.690 31 (1 row(s) affected)
Please correct me, if this is not the correct way of calculating the age. Comments are always welcome.
Sql server 2005 code:
DECLARE @DOB datetime
set @DOB =’05/01/1982′
SELECT @DOB ‘Date of Birth’, GETDATE() ‘Current Date’,
DATEDIFF(YEAR,@DOB,GETDATE())
–
(CASE
WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
> GETDATE() THEN 1
ELSE 0 END)
‘Age in Years’
What a complicated code. Just do this:
age=
floor(
( cast(convert(varchar(8),getdate(),112) as int)-
cast(convert(varchar(8),@dob,112) as int) ) / 10000
)
please tell me
how to calculate age on dob(from db)
–Create table variable to hold our test records
DECLARE @Workers TABLE (WorkerName VARCHAR(50), DOB DATE);
–Insert test records
INSERT INTO @Workers
SELECT ‘Ryan’, ‘1972-08-24’ UNION ALL
SELECT ‘James’, ‘1985-09-26’ UNION ALL
SELECT ‘Jasson’, ‘1983-08-25’ UNION ALL
SELECT ‘Tara’, ‘1991-09-24’ UNION ALL
SELECT ‘William’, ‘1992-08-19’ UNION ALL
SELECT ‘Judy’, ‘1989-09-15’;
–Variable to provide required number of days
DECLARE @InNextDays INT;
SET @InNextDays = 3;
— Query to find workers, whose birthday is in given number of days
SELECT *
FROM @Workers
WHERE DATEADD( Year, DATEPART( Year, GETDATE()) – DATEPART( Year, DOB), DOB)
BETWEEN CONVERT( DATE, GETDATE())
AND CONVERT( DATE, GETDATE() + @InNextDays);
This was just what I needed!!!
Thanks a million 🙂
The solution from Lodewijk 🙂
Thank you, I agree that yours is the simplest code. I knew there had to be a way being MS ACCESS can do it with the following code:
Drv_Age_Dschg: DateDiff(“yyyy”,[dbo_APCReportHeader].[Pat_Dob],[dbo_APCReportHeader].[Claim_Dschg_DT])+Int(Format([dbo_APCReportHeader].[Claim_Dschg_DT],”mmdd”)<Format([dbo_APCReportHeader].[Pat_Dob],"mmdd"))
I need To calculate age from dataofbirth column from table please help me..
Thanks in advance..
SELECT
DOB,
DATEDIFF(YY,DOB,GETDATE()) AS YEAR,
DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) AS BIRTHDAY,
CASE
WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE()
THEN DATEDIFF(YY,DOB,GETDATE()) -1
ELSE DATEDIFF(YY,DOB,GETDATE())
END as age
FROM WORKERS
SELECT
DOB,
DATEDIFF(YY,DOB,GETDATE()) AS YEAR,
DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) AS BIRTHDAY,
CASE
WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE()
THEN DATEDIFF(YY,DOB,GETDATE()) -1
ELSE DATEDIFF(YY,DOB,GETDATE())
END as age
FROM WORKERS
sir I want to make a function in which I pas a date and the function will calculate difference between passes date and getdate.
but not only year I want to year , month and days
like this
**years,**months,**days
declare @date_of_birth as datetime=’2015-12-31 10:00:01′;
Select cast((DATEDIFF(m, @date_of_birth, GETDATE())/12) as varchar) + ‘ Y & ‘ +
cast((DATEDIFF(m, @date_of_birth, GETDATE())%12) as varchar) + ‘ M & ‘ +
–cast((DATEDIFF(m, @date_of_birth, GETDATE())%31) as varchar) + ‘ D ‘
cast((datepart(d,GETDATE())-datepart(d,@date_of_birth)) as varchar) + ‘D’
as Age
DECLARE @Test TABLE (
EmpName VARCHAR(40) ,
BirthDate DATE
);
INSERT INTO @Test ( EmpName , BirthDate
)
VALUES ( ’30 Yrs old yesterday’ , DATEADD(day , -1 , DATEADD(year , -30 , GETDATE()))
) , ( ’30 Yrs old today’ , DATEADD(year , -30 , GETDATE())
) , ( ’30 Yrs old tomorrow’ , DATEADD(day , 1 , DATEADD(year , -30 , GETDATE()))
);
SELECT EmpName , BirthDate ,
CASE
WHEN DATEADD(YY , DATEDIFF(yy , BirthDate , GETDATE()) , BirthDate) < GETDATE()
THEN DATEDIFF(yy , BirthDate , GETDATE())
ELSE DATEDIFF(yy , BirthDate , GETDATE()) – 1
END AS Age
FROM @Test;
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.GetAge
(@DOB DATETIME,
@ToDate DATETIME
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Diff SMALLINT,
@F DATETIME,
@T DATETIME
SELECT @Diff = DATEDIFF(month, @DOB, @ToDate) / 12,
@F = DATEADD(year, 2000 – DATEPART(year, @DOB), @DOB),
@T = DATEADD(year, 2000 – DATEPART(year, @ToDate), @ToDate)
IF DATEDIFF(month, @DOB, @ToDate) % 12 = 0
BEGIN
IF @DOB @T
SELECT @Diff = @Diff – 1
IF @DOB > @ToDate AND @F < @T
SELECT @Diff = @Diff + 1
END
RETURN @Diff
END
CREATE FUNCTION [dbo].[ufn_GetAge]
(@pDateOfBirth DATETIME,
@pAsOfDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @vAge INT;
IF @pDateOfBirth >= @pAsOfDate
RETURN 0;
SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate);
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate)
OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate)
AND DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge – 1;
RETURN @vAge;
END;
GO
This solution works except for if somebody’s birthday is Feb 29th and its being measured on Feb 28th the following year. In some countries its the birthday but in most countries the legal birthday is March 1 the following (non-leap) years.
Create FUNCTION [dbo].[GetCurrentAge]
(
@DOB Date
)
RETURNS int
AS
BEGIN
Declare @CurrentAge int
Declare @Correction int
Select @Correction=0
Select @CurrentAge=DateDiff(YEAR,CONVERT(DATETIME, @DOB),GETDATE())
IF (DateAdd(YY,@CurrentAge,CONVERT(DATETIME, @DOB))>GETDATE())
BEGIN
Select @Correction=-1
END
Select @CurrentAge=@CurrentAge+@Correction
Return @CurrentAge
END
We can also use
select datediff(yy,birthdate,getdate()) as age from tablename
Where the birthdate is a column in table
Hi, How may I calculate th hours past from birth date of baby neonatal to deathdate?
here my query
SELECT d.YearMonth,
(DATEDIFF(hh,ud.Birthdate, ud.UnitDischargeDateTime)) % 24 AS Hours_Taken
,(DATEDIFF(dd,ud.Birthdate, ud.UnitDischargeDateTime)) AS Age_Days
,DATEDIFF(DAY, ud.Birthdate,ud.UnitDischargeDateTime) -(DATEDIFF(WEEK, ud.Birthdate,ud.UnitDischargeDateTime) * 2) as daylived
,DATEDIFF(hh,CONVERT(TIME, ud.Birthdate),CONVERT(TIME, ud.UnitDischargeDateTime))/24.0 as hourslived
,dateadd(hh, -24, DateDiff(hh, getdate(), ud.Birthdate)) as Last24H
FROM DB_MONITOR.dbo.report_SplitIntervalFirstLastDay(@BeginDate, @EndDate) as d,#temp_UnitAdmission as ud
JOIN #temp_NeonateUnit as neo ON neo.UnitId = ud.UnitId
AND ud.UnitDischargeCode = ‘D’
WHERE ud.UnitDischargeDateTime >= d.FirstDay AND ud.UnitDischargeDateTime <= d.LastDay
GROUP BY d.YearMonth, ud.Birthdate, ud.UnitDischargeDateTime;
Thanks in advancend
Hi, How are you? can you help?
How to calculate the hours past from birth date of baby neonatal to death date?
This is my query!
SELECT d.YearMonth,
(DATEDIFF(hh,ud.Birthdate, ud.UnitDischargeDateTime)) % 24 AS Hours_Taken
,(DATEDIFF(dd,ud.Birthdate, ud.UnitDischargeDateTime)) AS Age_Days
,DATEDIFF(DAY, ud.Birthdate,ud.UnitDischargeDateTime) -(DATEDIFF(WEEK, ud.Birthdate,ud.UnitDischargeDateTime) * 2) as daylived
,DATEDIFF(hh,CONVERT(TIME, ud.Birthdate),CONVERT(TIME, ud.UnitDischargeDateTime))/24.0 as hourslived
,dateadd(hh, -24, DateDiff(hh, getdate(), ud.Birthdate)) as Last24H
FROM DB_MONITOR.dbo.report_SplitIntervalFirstLastDay(@BeginDate, @EndDate) as d,#temp_UnitAdmission as ud
JOIN #temp_NeonateUnit as neo ON neo.UnitId = ud.UnitId
AND ud.UnitDischargeCode = ‘D’
WHERE ud.UnitDischargeDateTime >= d.FirstDay AND ud.UnitDischargeDateTime <= d.LastDay
GROUP BY d.YearMonth, ud.Birthdate, ud.UnitDischargeDateTime;
Thanks in advancend