SQL scrip to extract decimal portion from a value
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dpicco
Or you could convert it to a string and use the right() runction
Try:
Replace(varValue -fix(varValue),"0","",1,1)
Replace(varValue -fix(varValue),"0","",1,1)
Another idea
(Value MOD 365) / 365
mlmcc
(Value MOD 365) / 365
mlmcc
ASKER
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
{value} - round({value},0)
somevalue - floor(somevalue)
right() runction
Replace(varValue -fix(varValue),"0","",1,1)
(Value MOD 365) / 365
LOL. We already did!
ASKER
i meant with a "SELECT", etc. ... (sorry)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yea sorry about, i meant 1.24444. The 14.93333333 was the month pass.
"Hi the "fix" part didn't work"
This is for sql (access)!
This is for sql (access)!
ASKER
thanx all