Search code examples
sqloracletrim

Trim numbers after decimal in SQL in my equation

I have seen several questions similar to mine but cant seem to fine the answer. 

select 
c.claimnumber, 
to_char(c.RECEIPTDATE,'mm/dd/yyyy') as receiptdate,
(sysdate - c.RECEIPTDATE) as daysbetween
from claim c
where c.RECEIPTDATE > sysdate -10

The results come out something like 8.47980324074074074074074074 I'm looking to get 8.

I thought about using the round function but I really just want to get rid of the decimal and everything following.

Thanks in advance

Solution

  • Use the built in TRUNC() function which looks like this TRUNC( [number], [truncvalue] )

    From the oracle docs:

    number

    The number to truncate. The value specified for number must be followed by a comma.

    truncvalue

    An INTEGER value that specifies the number of places to the right or left of the decimal point to which number should be truncated. When truncvalue is positive, digits to the right of the decimal point are truncated. When it is negative, digits to the left of the decimal point are truncated (that is, made zero). When truncvalue is omitted, number is truncated to 0 decimal places.

    select c.claimnumber, 
       to_char(c.RECEIPTDATE,'mm/dd/yyyy') as receiptdate,
       trunc(sysdate - c.RECEIPTDATE) as daysbetween
from claim c
where c.RECEIPTDATE > sysdate -10