The TRUNCATE function (abbreviated as TRUNC) truncates either a number or a datetime value.

Syntax TRUNC (datetime, [fmt]) TRUNC (number,truncvalue) Key datetime A datetime expression that identifies a date and time number. fmt A text expression that specifies one of the date/time format models. number The number to truncate.truncvalueAn 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). If truncvalue is omitted,numberis truncated to 0 decimal places.

**Examples**

SQL> SHOW TRUNC (TO_DATE('27-OCT-11'),'YEAR') 01-JAN-11

The value specified for *format *will determine the value returned:

SQL> SHOW TRUNC(mydatetime, 'MON')

01-AUG-11

SQL> SHOW TRUNC(mydatetime, 'DD')

07-AUG-11 SQL> SHOW TRUNC(to_date('22-AUG-11'), 'Q') 01-JUL-11 SQL> SHOW TRUNC(123.4567, 2) 123.45

The following SQL calculates the time between two date/time fields dt1 and dt2 and gives you the result in the form hh:mi (eg 17:23)

This works because the database stores date/times as fractions of a day
so 06:00 is stored as 0.25. (The result contains a colon so it is a character value.)

trunc(24*(dt2-dt1))||':'||round(60*(24*(dt2-dt1)- trunc(24*(dt2-dt1))))

Hours between

trunc(24*(dt2-dt1))

Minutes between

round(60*(24*(dt2-dt1)-trunc(24*(dt2-dt1))))

**Related**

TO_CHAR - Convert to character String

TO_NUMBER - Convert to numeric format

TRUNCATE TABLE

Oracle SQL Functions

Copyright © 1999-2021 SS64.com

Some rights reserved

Some rights reserved