Oracle PL/SQL - TRUNC and ROUND Date Values

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. DATE data type in PL/SQL

Introduction

The TRUNC and ROUND built-in functions, typically applied to numbers, also work with dates and timestamps.

The TRUNC function truncates the date to some level of precision, whereas ROUND rounds the date to a specified point: 

v_dt:= TRUNC (DATE|TIMESTAMP|INTERVAL value [,PRECISION]);
v_dt:= ROUND (DATE|TIMESTAMP|INTERVAL value [,PRECISION]);

Here you use date format masks. For example, 'YYYY' truncates the date to years, and 'MM'to a month.

If you don't specify any precision, the default is day ( 'DD').

You can't specify a combination of format masks.

Only one can be specified at a time.

Demo

SQL>
SQL> declare--  w w  w. j a v a  2s.co m
  2      v_dt DATE;
  3      v_form_tx VARCHAR2(25):='YYYY-MM-DD HH24:MI:SS';
  4  begin
  5      v_dt:=trunc(sysdate);
  6      DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
  7      v_dt:=trunc(sysdate,'YYYY');
  8      DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
  9      v_dt:=round(sysdate,'HH');
 10      DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
 11  end;
 12  /
2018-04-21 00:00:00
2018-01-01 00:00:00
2018-04-21 10:00:00

PL/SQL procedure successfully completed.

SQL>
  • TRUNC without a format mask rounds the current date to midnight.
  • 'YYYY' format mask rounds to midnight of the new year
  • ROUND with format 'HH' rounds the date to the beginning of the hour
  • TRUNC (value,'W') gets the first day of the week.

Related Topic