Get age from date of birth field | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Get age from date of birth field

I want to write query to retrieve records from my employee table which the employee age is greater than 25 and is less than 30, in my table i have only date of birth field, i don’t have age field in my table. Any one please help me out how to write in single query? Thanking you,
WHEN dateadd(year, datediff (year, DateOfBirth, GetDate()), DateOfBirth) > GetDate()
THEN datediff (year, DateOfBirth, GetDate()) – 1
ELSE datediff (year, DateOfBirth, GetDate())
END as Age
FROM whatever where….
Mohammed U.

Select columns from table
Where datediff(year,DOB,Getdate()) between 26 and 30 Madhivanan Failing to plan is Planning to fail
Using the simple DATEDIFF approach will yield incorrect result in certain cases. Consider this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @geburtstag DATETIME, @myDate DATETIME<br />SELECT @geburtstag = ‘19680723’, @myDate = ‘20060101’<br />SELECT DATEDIFF(YEAR, @geburtstag, @mydate) — wrong! At that point I was obviously just 37 !!! <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /> <br />———– <br />38<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />You can use one of the following methods in your WHERE clause:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT <br /> DATEDIFF(yy, @geburtstag, @myDate) -<br /> CASE <br /> WHEN (MONTH(@myDate) * 100 + DAY(@myDate)) &lt;<br /> (MONTH(@geburtstag)* 100 + DAY(@geburtstag))<br /> THEN 1 <br /> ELSE 0 <br /> END<br /> <br />SELECT <br /> (CAST(CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@myDate, 112) AS INT)<br /> – <br /> CAST(CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @geburtstag, 112) AS INT))/10000<br /><br />SELECT <br /> YEAR(@myDate) – YEAR(@geburtstag) -<br /> (CASE <br /> WHEN (MONTH(@geburtstag) &gt; MONTH(@myDate))<br /> OR (MONTH(@geburtstag) = MONTH(@myDate)<br /> AND DAY(@geburtstag) &gt; DAY(@myDate))<br /> THEN 1<br /> ELSE 0 END)<br /><br />SELECT <br /> (0+CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@myDate, 112)<br /> – <br /> CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @geburtstag, 112))/10000<br /> <br />———– <br />37<br /><br />(1 row(s) affected)<br /><br /> <br />———– <br />37<br /><br />(1 row(s) affected)<br /><br /> <br />———– <br />37<br /><br />(1 row(s) affected)<br /><br /> <br />———– <br />37<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=></a>