We help IT Professionals succeed at work.

SQL scrip to extract decimal portion from a value

696 Views
Last Modified: 2012-05-09
We need to extract the decimal value from a result; that is where the result is 2.4574, I want to have the decimal value of .4574.to work with.   We need this with any result; for example:

result of 5.2584, we need .2584
result of 11.3582, we need .3582
result of   6.9884, we need .9884
result of   0.635, we need .635

In essence, in order to give more infor of what's needed, we need to work with the excess of period of years.  For example:

subtract 02/01/2008 from 07/07/2010, and you get 887 days, divide that by 365, you get 2.430136986.

We need to extract and work with the decimal portion of .430136986.




Comment
Watch Question

Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Or you could convert it to a string and use the right() runction
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Try:
Replace(varValue -fix(varValue),"0","",1,1)
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Another idea

(Value MOD 365) / 365

mlmcc

Author

Commented:
Hi can you guys give an example with a sql script for  these

{value} - round({value},0)
somevalue - floor(somevalue)
right() runction
Replace(varValue -fix(varValue),"0","",1,1)
(Value MOD 365) / 365
Top Expert 2010

Commented:
LOL.  We already did!

Author

Commented:
i meant with a "SELECT", etc. ... (sorry)
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Hamed NasrRetired IT Professional
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi the "fix" part didn't work, but all others worked excellently.

One last related question, since we need for excess of a period of year, the decimal value,, can we use something directly on the script for dates?  For example:

if start year is 4/15/2009 and ending year is 7/7/2010, the years passed is 14.93333333.  Is there way to calculate it directly on the dates?  (extract the .9333333 with some sort date function)


Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
yea sorry about, i meant 1.24444.  The 14.93333333 was the month pass.
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
"Hi the "fix" part didn't work"
This is for sql (access)!

Author

Commented:
thanx all

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.