Example 2.938 = 938
Thanks
+6
A:
one way, works also for negative values
declare @1 decimal(4,3)
select @1 = 2.938
select PARSENAME(@1,1)
SQLMenace
2010-08-05 19:53:25
+1 Clever use of PARSENAME.
Joe Stefanelli
2010-08-05 19:58:12
So what if the value is .042 it only returns 42 then?
2010-08-05 20:07:08
no, it returns 042, both of these return 042--> SELECT PARSENAME(0.042,1), PARSENAME(.042,1)
SQLMenace
2010-08-05 20:10:01
If you want only 42, then select cast(PARSENAME(@1,1) as int)
Joe Stefanelli
2010-08-05 20:10:32
Hmmm for some reason I'm getting 42--Get remainder of day that was not workedDECLARE @NotWorked DECIMAL(4,3)SET @NotWorked = (@AllMins/@MinsInDay)DECLARE @Remainder INTSET @Remainder = PARSENAME(@NotWorked,1)42 and 3.042All Mins is INT MinsInDay is Decimal so 4380/1440
2010-08-05 20:13:05
I don't know what your numbers are but try this DECLARE @NotWorked DECIMAL(5,3) SET @NotWorked = (144/4380.0) select PARSENAME(@NotWorked,1)it gives me 033
SQLMenace
2010-08-05 20:20:01
IS there a way to do this without the select I am actually try to give a @Remainder variable the value.... which in your can above would be the 033 or the number after the decimal
2010-08-05 20:24:22
can't be an int since an int will dismiss the first 0, use varchar, example DECLARE @Remainder varchar(10)DECLARE @NotWorked DECIMAL(5,3) SET @NotWorked = (144/4380.0) SELECT @Remainder = PARSENAME(@NotWorked,1)select @Remainder -- see 033
SQLMenace
2010-08-05 20:27:17
Awessssommmmeee Thanks!!!
2010-08-05 20:28:55
+1 damn, that is slick
kekekela
2010-08-06 01:51:33
+3
A:
You can use FLOOR:
select x, ABS(x) - FLOOR(ABS(x))
from (
select 2.938 as x
) a
Output:
x
-------- ----------
2.938 0.938
Or you can use SUBSTRING:
select x, SUBSTRING(cast(x as varchar(max)), charindex(cast(x as varchar(max)), '.') + 3, len(cast(x as varchar(max))))
from (
select 2.938 as x
) a
RedFilter
2010-08-05 19:53:53
Now try it for negative numbers: `SELECT -0.25 - FLOOR(-0.25)` gives 0.75.
Mark Byers
2010-08-05 19:56:20
Added [ABS](http://msdn.microsoft.com/en-us/library/ms189800%28SQL.90%29.aspx) to handle negative numbers...
OMG Ponies
2010-08-05 19:57:48
I like converting to a varchar and using the index of . to substr, works for any decimal places.
Brandon Horsley
2010-08-05 20:00:52
+2
A:
The usual hack (which varies a bit in syntax) is
x - floor(x)
That's the fractional part. To make into an integer, scale it.
(x - floor(x)) * 1000
S.Lott
2010-08-05 19:54:14
A:
If you know that you want the values to the thousandths, place, it's
SELECT (num - FLOOR(num)) * 1000 FROM table...;
Seamus Campbell
2010-08-05 19:55:24