Rounding down a number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rounding down a number

Author

Topic

bhussey
Starting Member

5 Posts

Posted - 2012-09-24 : 15:00:33

I am writing a query that will show an employee age to determine if they are eligible for a certain benefit. If the employee is not 21 years of age at the time this query is run they are not eligible.

select round((sysdate-birthdate)/365.25),2
from server.table
where employee = '1234'

Result = 20.78370525642000659112226531802164930157

I'd actually like to see the Result = 20

Thank you

singularity
Posting Yak Master

153 Posts

Posted - 2012-09-24 : 15:02:22

You can use the floor function to round down:

http://msdn.microsoft.com/en-us/library/ms178531.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-24 : 15:55:29

Can you just CAST the result to an INTEGER?

bhussey
Starting Member

5 Posts

Posted - 2012-09-24 : 18:01:40

The floor function works, thanks for the help

bhussey
Starting Member

5 Posts

Posted - 2012-09-24 : 18:05:35

I do have a additional question for this. Now I need to identify if this result is 21 or greater. I tried
(sysdate-birthdate)/365.25) >= '21' but no results

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-24 : 18:58:52

A couple of things:

1. The FLOOR function will return the same data type as the expression being floored. What that data type is in your case, I'm not sure. It might even be a float but, I suspect it's Numeric. If it is a Float/Real, that might cause comparison issues.
2. You have wrapped the value-literal in single quotes ('21') that denotes a string. I'd have to look up the casting precedence to know for sure what SQL is doing, but that is probably your issue. You should not rely on implicit casting by SQL, rather you should explicitly cast the to the data type you want it to be.

CAST(((sysdate-birthdate)/365.25) AS INT) >= 21

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources