InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:08:05 GMT20http://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxHmmmm.... something must be wrong on my end. It keeps giving me a 404 error.Q & A's are sometimes pretty rich in information like the little date jewel you found.Mon, 05 Oct 2009 07:08:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxSeems fine now, just a temporary thing i guess.TBH , its hardly worth the click though :-) simple Q and AMon, 05 Oct 2009 07:05:17 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxShoot... the link is broken. Thanks anyway, Dave.Mon, 05 Oct 2009 05:58:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxCant take the credit for it ...[url]http://windowsitpro.com/article/articleid/14267/how-can-i-calculate-someones-age-in-sql-server.html[/url]Mon, 05 Oct 2009 02:34:17 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspx[quote][b]Dave Ballantyne (10/2/2009)[/b][hr]Heres how to calculate an age[code="sql"]select CASEWHEN dateadd(year, datediff (year, d1, d2), d1) > d2THEN datediff (year, d1, d2) - 1ELSE datediff (year, d1, d2)END as Age[/code]I'll take a look at the rounding issue though...[/quote]That's one of the simplest I've seen. Correctly handles the Feb 29 problem that most other's don't handle, too!Fri, 02 Oct 2009 19:38:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxFor some reason that way of rounding was in my head. But I spoke with others about and I think SQL Server does round it correctly yes.Fri, 02 Oct 2009 06:40:52 GMTESBhttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxI think you are wrong in your assessment of round though,round(60.444445,1) = 60.4 This [code="sql"]PRINT ROUND(ROUND(ROUND(ROUND(ROUND(60.444445, 5), 4), 3), 2), 1)[/code]works because you are rounding a rounded , rounded, rounded , rounded number (ie the 5 has for want of a better word been cascaded up)Fri, 02 Oct 2009 04:33:26 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxHeres how to calculate an age[code="sql"]select CASEWHEN dateadd(year, datediff (year, d1, d2), d1) > d2THEN datediff (year, d1, d2) - 1ELSE datediff (year, d1, d2)END as Age[/code]I'll take a look at the rounding issue though...Fri, 02 Oct 2009 04:16:52 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic796803-338-1.aspxHello. I use SQL Server 2005 and I'm calculating the age of patients in the following manner:[code="sql"]DATEDIFF(DAY, birthdate, operationdate)/365.25[/code]The output always seems to be a decimal with 6 decimal digits. The rounding problem arises with border cases like for instance an age of 60.444445. If you round this properly it should result in 60.45 and that should result in 60.5. However, when I use [font="Courier New"]ROUND(60.444445, 1)[/font] it results in 60.4!The problem is that SQL Server seems to ignore the digits after the rounding border. [font="Courier New"]ROUND(60.449999, 1)[/font] for instance also results in 60.4. It seems to truncate it 1 position behind the rounding index and starts rounding after that. I found out that using the ROUND() function repeatedly in the following manner solves the issue but it is a bit ridiculous:[code="sql"]PRINT ROUND(ROUND(ROUND(ROUND(ROUND(60.444445, 5), 4), 3), 2), 1)[/code]Is there any other way to solve this and am I missing something?Thanks in advance!Fri, 02 Oct 2009 04:00:57 GMTESB