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.
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')
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.)
TO_CHAR - Convert to character String
TO_NUMBER - Convert to numeric format
Oracle SQL Functions