SQL how to round down an integer value to 2 decimal places-CodePudding

I am trying to perform a SQL query which pulls a value to 2 decimal places as depicted below. The issue is the value is actually 8.498, where it is outputted as 8.50 and I need it in my case to be outputted at 8.49 instead.

This is my current code below, is there a way that I can use FLOOR to output the value as 8.49

SELECT gas_date as 'time', provisional_price
from INT654
where provisional_price  is not null AND hub_name = 'Sydney' AND provisional_schedule_type = 'D-2'
order by gas_date desc limit 1

CodePudding user response：

If you actually want to round down, you have to do:

floor(provisional_price*100)*.01

But if you really want to round towards zero (down for positives, up for negatives), you can do:

truncate(provisional_price,2)

CodePudding user response：

You can use the below function like SELECT ROUND(8.498, 2, 1) AS RoundValue; where the syntax looks like ROUND(NUMBER TO BE ROUNDED OF, NO.OF PLACES TO BE ROUNDED OFF,OPERATION) where the Operation is optional one.if you give 0 it is rounded off the result to the number of decimal if you give other than 0 it truncates the result to the number of decimals and default value is 0. You can try this function in any online sql practice platform