Oracle/PLSQL: Trunc Function (with dates)_oracle with date-CSDN博客

本文详细介绍了Oracle PL/SQL中TRUNC函数的使用方法及其参数，包括日期时间的截断规则，并通过具体示例展示了如何在SQL查询中应用该函数。

Oracle/PLSQL: Trunc Function (with dates)

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

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 To:

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

For example:

trunc(to_date('22-AUG-03'), 'YEAR')	would return '01-JAN-03'
trunc(to_date('22-AUG-03'), 'Q')	would return '01-JUL-03'
trunc(to_date('22-AUG-03'), 'MONTH')	would return '01-AUG-03'
trunc(to_date('22-AUG-03'), 'DDD')	would return '22-AUG-03'
trunc(to_date('22-AUG-03'), 'DAY')	would return '17-AUG-03'