Oracle TRUNC (date) Function - W3resource

Trang chủ » Hàm Trunc Trong Oracle » Oracle TRUNC (date) Function - W3resource

Có thể bạn quan tâm

Oracle TRUNC (date) function Last update on September 24 2024 10:21:25 (UTC/GMT +8 hours)

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, such as day, month, or year. It operates according to the rules of the Gregorian calendar.

Uses of Oracle TRUNC (date) Function
  • Date Normalization: Remove the time portion of a date to work with date-only values.
  • Time-Based Calculations: Truncate dates to specific units (day, month, year) for calculations.
  • Data Grouping: Group data by truncated dates, such as by year, quarter, or month.
  • Comparisons: Compare dates without considering the time component for accurate results.
  • Date Formatting: Format dates to display specific parts like year, month, or day for reports.

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

Pictorial Presentation of Oracle trunc date function

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

Từ khóa » Hàm Trunc Trong Oracle