Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Date Functions / Oracle TRUNC

Oracle TRUNC

The Oracle TRUNC() function returns a DATE value truncated to a specified unit.

Syntax

The following shows the syntax of the Oracle TRUNC() function:

TRUNC(date, format)

Arguments

The TRUNC() function accepts two arguments:

1) date
The date argument is a DATE value or an expression that evaluates to a DATE value that will be truncated.

2) format
The format argument determines the unit to which the  date will be truncated.

The format argument is optional. Its default value is DD that instructs the TRUNC() function to truncate the date to midnight.

The following table illustrates valid values for the format argument:

FormatDescription
CC, SCCCentury, with or without minus sign (BC)
[S]YYYY, [S]YEAR, YYY, YY, YYear (in various appearances)
IYYY, IYY, IY, IISO year
QQuarter
MONTH, MON, MM, RMMonth (full name, abbreviated name, numeric, Roman numerals)
IW, WW (ISO)week number
WDay of the week
DDD, DD, JDay (of the year/of the month/Julian day)
DAY, DY, DClosest Sunday
HH, HH12, HH24Hours
MIMinutes

Return value

The TRUNC() function returns a DATE value truncated to a specified unit.

Examples

Let’s look at some examples of using the Oracle TRUNC() function.

A) Truncate a date value using default format

Consider the following date time value:

04-Aug-2017 15:35:32

The following statement truncates the date value to midnight:

SELECT TO_CHAR( TRUNC(TO_DATE( '04-Aug-2017 15:35:32 ', 'DD-Mon-YYYY HH24:MI:SS' )), 'DD-Mon-YYYY HH24:MI:SS' ) result FROM dual;

Output:

RESULT -------------------- 04-Aug-2017 00:00:00

In this example,

  • First, the TO_DATE() function converted a date string to a DATE value.
  • Second, the TRUNC() function truncated the date. Because we did not pass the format argument, the TRUNC() function uses the default value that truncates the date to midnight.
  • Third, the TO_CHAR() function formatted the result of the TRUNC() function.

B) Get the first day of the month of a date

The following statement returns the first day of the current month.

SELECT TRUNC( SYSDATE, 'MM' ) result FROM dual;

Output:

RESULT --------- 01-MAY-20

If you want to return the first day of the month of a specific date, you just need to use that date instead of the SYSDATE

C) Get the first day of the quarter of a date

Similarly, you can get the first day of the current quarter:

SELECT TRUNC( SYSDATE, 'Q' ) result FROM dual;

Output:

RESULT --------- 01-APR-20

In this example, we replaced the month ( MM) by quarter ( Q).

In this tutorial, you have learned how to use the Oracle TRUNC() function to truncates a date value to a specified unit.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle TO_CHAR
Next Oracle TZ_OFFSET

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

Oracle Date Functions

  • ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEXT_DAY
  • NEW_TIME
  • ROUND
  • SESSIONTIMEZONE
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR
  • TO_DATE
  • TRUNC
  • TZ_OFFSET

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.