How to calculate age from date of birth in years.months format
5 answers
Maybe something like this
Declare @dateofbirth datetime
Declare @currentdatetime datetime
Declare @years varchar(40)
Declare @months varchar(30)
Declare @days varchar(30)
set @dateofbirth='1986-03-15'
set @currentdatetime = getdate()--current datetime
select @years=datediff(year,@dateofbirth,@currentdatetime)-- To find Years
select @months=datediff(month,@dateofbirth,@currentdatetime)-(datediff(year,@dateofbirth,@currentdatetime)*12)
-- To Find Months
select @days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)-- To Find Days
select @years +' years, ' +@months +' months, '+@days +' days' asYearMonthDay
will be
29 years, 2 months, -10 days
Refresh
A single line query would be
SELECT CAST(DATEDIFF(YEAR,'2000-06-01 10:00:01',GETDATE()) AS VARCHAR(10)) + '.' + CAST(DATEDIFF(MONTH,'2000-06-01 10:00:01',GETDATE())-(DATEDIFF(YEAR,'2000-06-01 10:00:01',GETDATE())*12) AS VARCHAR(10)) AS Age
+1
source to share
Try the following:
declare @DateOfBirth date= '1999-06-1'
Select AgeYears=DATEDIFF(year,@DateOfBirth ,getdate())- CASE WHEN MONTH(getdate())*100+DAY(getdate())<MONTH(@DateOfBirth)*100+DAY(@DateOfBirth) then 1 else 0 end
,AgeMoanths=(DATEDIFF(month,@DateOfBirth,getdate()) - CASE WHEN DAY(getdate())<DAY(@DateOfBirth) THEN 1 ELSE 0 END ) % 12
,Agedays=DATEDIFF (day,dateadd(month,(DATEDIFF(month,@DateOfBirth,getdate()) - CASE WHEN DAY(getdate())<DAY(@DateOfBirth) THEN 1 ELSE 0 END ) ,@DateOfBirth) ,getdate())
+1
source to share
Try it like this:
DECLARE @setdate datetime
,@current datetime
SET @setdate = '20000601'
SET @current = GETDATE()
SELECT Years=DATEDIFF(year,@setdate,@current )
- CASE WHEN MONTH(@current )*100+DAY(@current )<MONTH(@setdate)*100+DAY(@setdate) THEN 1 ELSE 0 END
, Months=( DATEDIFF(month,@setdate,@current )
- CASE WHEN DAY(@current )<DAY(@setdate) THEN 1 ELSE 0 END ) % 12
And to get the result in 14.11 format try this:
DECLARE @setdate datetime
,@current datetime
SET @setdate = '20000601'
SET @current = GETDATE()
SELECT CAST((DATEDIFF(year,@setdate,@current )
- CASE WHEN MONTH(@current )*100+DAY(@current )<MONTH(@setdate)*100+DAY(@setdate) THEN 1 ELSE 0 END) AS varchar(10))
+'.' + CAST((( DATEDIFF(month,@setdate,@current )
- CASE WHEN DAY(@current )<DAY(@setdate) THEN 1 ELSE 0 END ) % 12) AS varchar(10))
0
source to share
Declare @DOB varchar(20)
Set @DOB ='1989-12-15 10:00:01' SELECT CAST(DATEDIFF(YEAR,@DOB,GETDATE()) AS VARCHAR(10)) + 'Years'
+ CAST(DATEDIFF(MONTH,@DOB,GETDATE())-(DATEDIFF(YEAR,@DOB,GETDATE())*12) AS VARCHAR(10)) + 'Months'
+ CAST(DATEDIFF(dd,@DOB,GETDATE())-(DATEDIFF(year,@DOB,GETDATE())*1461/4) AS VARCHAR(10)) +'Days'
0
source to share