Return the TENTHS - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Return the TENTHS

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-06-18 : 00:23:23

Good day!

Could anyone help me to return the "TENTHS" value of a given number?

let say ...

Number     Tenths
--------------------------
1050       50
955        55
195.28     95.28

I tried using the modulus operator however it wont work on a number that has a decimal value.

How? TNX!

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-18 : 01:28:32

It is better to do this in your presentation layer
Otherwise you need to convert it into varchar and extract the digits from that

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-06-18 : 01:33:41

tnx for the suggestion madhivanan

Im writing this on a stored proc and this is my basis of my computation.

Pls show how?

PLAN YOUR WORK, WORK YOUR PLAN. :-)

Want Philippines to become 1st World COuntry? Go for World War 3...

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-18 : 01:34:43

If you need to do calc with it you might be able to get away with something like this:

declare @your_num decimal(10,2])
set @your_num = '175.25'
select @your_num

-- divide by 100 to move desired digits to right of decimal
select @your_num = @your_num/100
select @your_num

-- subtract whole number value remaining on the left
select @your_num = @your_num - round(@your_num, 0, 1)
select @your_num

-- multiply by 100 to move them back to the left of dec
select @your_num = @your_num * 100
select @your_num

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-06-18 : 01:41:11

TNX nathans!

how about returning including the decimals?

PLAN YOUR WORK, WORK YOUR PLAN. :-)

Want Philippines to become 1st World COuntry? Go for World War 3...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-06-18 : 01:41:29

TNX nathans!

how about returning including the decimals?

PLAN YOUR WORK, WORK YOUR PLAN. :-)

Want Philippines to become 1st World COuntry? Go for World War 3...

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-18 : 02:33:01

quote:
how about returning including the decimals?

They should be there? Did you run the code i posted?

Just curious, what is the purpose of this?

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-06-18 : 02:36:27

declare @your_num decimal(10,2)
set @your_num = '175.25'
select @your_num
--175.25

-- divide by 100 to move desired digits to right of decimal
select @your_num = @your_num/100
select @your_num
--1.75

-- subtract whole number value remaining on the left
select @your_num = @your_num - round(@your_num, 0, 1)
select @your_num
--.75

-- multiply by 100 to move them back to the left of dec
select @your_num = @your_num * 100
select @your_num
--75.00

result should be 75.25

Want Philippines to become 1st World COuntry? Go for World War 3...

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-18 : 02:44:21

oops... im away from the server so I cant run this right now :)

Increase the scale of the decimal to account for the extra digits.

ex.

declare @your_num decimal(10,10)

Then just cast back to 2 to drop the trailing zeros.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-18 : 08:56:16

Take the mod and add the decimal part:

SELECT Number, CAST(Number AS INT)%100 + Number - FLOOR(Number) AS TENTHS
FROM(
	SELECT 2050 AS Number UNION ALL
	SELECT 955 UNION ALL
	SELECT 195.28) nrs

Number   TENTHS           
-------- ---------------- 
2050.00  50.00
955.00   55.00
195.28   95.28

rockmoose

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-06-20 : 00:14:08

TNX! rockmoose

its a credit. :D

the FLOOR function is what im looking for. just like the fix function in VB.

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources