Oracle TRUNC (date) function
How to truncate date functions in Oracle?
The TRUNC (date) function is used to get the date with the time portion of the day truncated to a specific unit of measure. It operates according to the rules of the Gregorian calendar.
Syntax:
TRUNC(date [, fmt ])
Parameters:
Name | Description |
---|---|
date | The date to truncate. |
fmt (Optional) |
The unit of measure for truncating. If fmt is not present, then the date is truncated to the nearest day. |
Return Value Type:
The value returned is always of datatype DATE
Date format models for the TRUNC function:
Format Model | Rounding Unit |
---|---|
CC SCC | One greater than the first two digits of a four-digit year |
SYYYY YYYY YEAR SYEAR YYY YY Y | Year (rounds up on July 1) |
IYYY IY IY I | ISO Year |
Q | Quarter (rounds up on the sixteenth day of the second month of the quarter) |
MONTH MON MM RM | Month (rounds up on the sixteenth day) |
WW | Same day of the week as the first day of the year |
IW | Same day of the week as the first day of the ISO year |
W | Same day of the week as the first day of the month |
DDD DD J | Day |
DAY DY D | Starting day of the week |
HH HH12 HH24 | Hour |
MI | Minute |
Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Pictorial Presentation

Example: Oracle TRUNC (date) function
The following statement truncates a specified date :
SELECT TRUNC(TO_DATE('02-MAR-15','DD-MON-YY'), 'YEAR')
"New Year" FROM DUAL;
Sample Output:
New Year --------- 01-JAN-15
Previous:
TO_YMINTERVAL
Next:
TZ_OFFSET
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook