Oracle trunc usage

In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure.
The syntax for the trunc function is:
Trunc (date, [format])
Date is the date to truncate.
Format is the unit of measure to apply for truncating. if the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

Below are the valid format parameters:

 

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

 

Applies:
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

Trunc (to_date ('22-AUG-03 '), 'Year') wo'd return '01-JAN-03' trunc (to_date ('22-AUG-03 '), 'q ') wo'd return '01-JUL-03 'trunc (to_date ('22-AUG-03 '), 'month') wo'd return '01-AUG-03 'trunc (to_date ('22-AUG-03 '), 'ddd ') wo'd return '22-AUG-03' trunc (to_date ('22-AUG-03 '), 'day') wo'd return '17-AUG-03' -- Oracle trunc () function usage
********************/
1. select trunc (sysdate) from dual -- 2011-3-18 today's date is 2011-3-18
2. select trunc (sysdate, 'mm') from dual -- 2011-3-1 returns the first day of the current month.
3. select trunc (sysdate, 'yy') from dual -- 2011-1-1 return the first day of the current year
4. select trunc (sysdate, 'dd') from dual -- 2011-3-18 return current year month day
5. select trunc (sysdate, 'yyyy') from dual -- 2011-1-1 return the first day of the current year
6. select trunc (sysdate, 'D') from dual -- 2011-3-13 (Sunday) returns the first day of the current week
7. select trunc (sysdate, 'hh') from dual -- 2011-3-18 14:00:00 current time is
8. select trunc (sysdate, 'mi') from dual -- 2011-3-18 14:41:00 the TRUNC () function has no precision in seconds.
********************/
/*
TRUNC (number, num_digits)
Number.
Num_digits is used to specify the number to take an integer. The default value of Num_digits is 0.
When TRUNC () function is intercepted, No rounding is performed.
*/
9. select trunc (123.458) from dual -- 123
10. select trunc (123.458, 0) from dual -- 123
11. select trunc (123.458, 1) from dual -- 123.4
12. select trunc (123.458,-1) from dual -- 120
13. select trunc (123.458,-4) from dual -- 0
14. select trunc (123.458, 4) from dual -- 123.458
15. select trunc (123) from dual -- 123
16. select trunc (123) from dual --
17. select trunc (123,-1) from dual -- 120
Author: "angels falling into the mortal world"