TRUNC function

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.

   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). 
              If truncvalue is omitted, number is truncated to 0 decimal places.

Examples

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

The value specified for format may 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 SS64.com 1999-2014
Some rights reserved