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
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework