Compute the daily and hourly salary for staff, truncate to the nearest penny rather than round-CodePudding

I have been asked to issue a query to answer a question, the query i have issued is:

SELECT SNAME, TO_CHAR(SAL,'L9,999') AS MONTHLY, TO_CHAR(SAL/22, 'L999.99') AS DAILY, TO_CHAR(SAL/22/8, 'L99.99') AS HOURLY FROM STAFF

But this time i need to truncate to the nearest penny rather than round, any help would be appreciated.

CodePudding user response：

Multiple by 100 (so pennies are the units rather than decimals), truncate to the nearest penny and then divide by 100:

SELECT SNAME,
       TO_CHAR(SAL,'L9,999') AS MONTHLY,
       TO_CHAR(TRUNC(SAL/22*100)/100, 'L999.99') AS DAILY,
       TO_CHAR(TRUNC(SAL/22/8*100)/100, 'L99.99') AS HOURLY
FROM   STAFF

or, use the second argument for TRUNC to specify the number of deciml places to truncate to:

SELECT SNAME,
       TO_CHAR(SAL,'L9,999') AS MONTHLY,
       TO_CHAR(TRUNC(SAL/22, 2), 'L999.99') AS DAILY,
       TO_CHAR(TRUNC(SAL/22/8, 2), 'L99.99') AS HOURLY
FROM   STAFF