SQL Server Performance Forum – Threads Archive
Get age from date of birth field
Hi,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,
Lalitha.C
SELECT CASE
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)) <<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) > MONTH(@myDate))<br /> OR (MONTH(@geburtstag) = MONTH(@myDate)<br /> AND DAY(@geburtstag) > 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=http://www.insidesql.de>http://www.insidesql.de</a>
]]>