SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Which Datatype to Use?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlslick
Yak Posting Veteran

83 Posts

Posted - 11/29/2012 :  08:53:10  Show Profile  Reply with Quote
Hey guys,

I have 2 variables below:

@Target (FLOAT)
@Days (INT)

@Days = 182

I am trying to do assign the following calculation to @Target:

@Target = 6/365*@Days

The arithmetic operation above equals a value of 2.99178082 but I need it to display as 3.0 only. However, I keep getting 0 only and I'm not understanding why. I think it is the Datatype I am using but I have tried DECIMAL(3, 3) without success.

Please help!

Thanks,
John

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 11/29/2012 :  08:57:12  Show Profile  Reply with Quote
@Target = 6/(365.0)*Days

(OR)

@Target = 6.0/365*Days

--
Chandu

Edited by - bandi on 11/29/2012 08:58:23
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  08:59:27  Show Profile  Reply with Quote
This is because of INTEGER division. Force it to be floating point by doing one of the following:
@Target = 6E/365*@Days
@Target = 6.0/365*@Days

You can use ROUND function or cast to decimal ( decimal(18,0) for example) to round off the decimal places.

Edited by - sunitabeck on 11/29/2012 09:00:45
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 11/29/2012 :  09:01:14  Show Profile  Reply with Quote
Wow, that easy eh? I just applied the ROUND() function too and it works perfect!!!

Thank you so much!!!
John
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  09:25:03  Show Profile  Reply with Quote
Yes:)

More often than not, I end up using CAST to decimal rather than ROUND because of the seemingly rational, but very irrational behavior of ROUND function under certain conditions. If you are interested, the gory details are here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158176
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000