I have one doubt in sql

Amudhan Kuppan

8y1.1kSQL

hi,

anyone help me how can i calculate the age in my table using sql

what can i query written i will send one image please note that

ALTER TABLE Student ADD Age AS DATEDIFF(YEAR, DateOfBirth, GETDATE())

this query can be written in my table

my problem is i will insert new record at dob column is ;1983-11-16' so it display age as 35

but iam checking age calculator website it display 34

correct answer is 34 i know what query can be written in sql become as 34 please help me

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Pansuriya8y ago
Accepted answer
Try this
DECLARE @dob  datetime
SET @dob='1991-05-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

Output:
AgeYearsDecimal                         AgeYearsIntRound AgeYearsIntTrunc
--------------------------------------- ---------------- ----------------
26.832306                               27               26

(1 row(s) affected)
In your case:
DECLARE @dob  datetime
SET @dob='1983-11-16 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
Output :
AgeYearsDecimal AgeYearsIntRound AgeYearsIntTrunc
34.309377 34 34
Amudhan Kuppan8y ago
thank you piyush,pramod,Bharanidharan
Pramod Thakur8y ago
You can write a query like this.
select datediff(month,'1983-11-16',getdate())/12 as [Year], datediff(month,'1983-11-16',getdate())%12 as [Month]
Bharanidharan Palanisamy8y ago
Try this,

select datediff(dd,'1983/11/16',getdate()) / 365 from