I have one doubt in sql

Question

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

Piyush Pansuriya · 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 Kuppan · Answer

thank you piyush,pramod,Bharanidharan

Pramod Thakur · Answer

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 Palanisamy · Answer

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