TRUNC and ROUND Functions In Oracle Date

TRUNC and ROUND Functions In Oracle Date | The ROUND and TRUNC functions in Oracle also can be used with Date functions. Here we will discuss the Oracle SQL TRUNC date function. How to use TRUNC SYSDATE in the Oracle database.

The Oracle date type contains two parts. These are:
1) Date part
2) Time portion

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') 
FROM dual;

Output:-

TO_CHAR(SYSDATE,’DD-
--------------------
05-JUL-2020 20:15:49

Date part:- 05-JUL-2020
Time portion:- 20:15:49

In the Oracle database, whenever we are using round(), and trunc() functions then the date part will be changed based on the time portion, and also time portion is automatically set to zeros.

While using the ROUND() function, the Oracle server checks the time portion. If the time portion is greater than or equal to 12 noon then automatically 1 day is added to the date part and the time portion is automatically set to zero. Example:-

SELECT TO_CHAR( ROUND(SYSDATE), 'DD-MON-YYYY HH24:MI:SS') 
FROM dual;

Output:-

TO_CHAR(ROUND(SYSDAT
--------------------
06-JUL-2020 00:00:00

The current time was 05-JUL-2020 at 20:15:49 but after applying the ROUND function we get 06-JUL-2020 at 00:00:00

Whenever we are using the TRUNC() function then the Oracle server doesn’t add one day into the date part if the time portion is greater than or equal to 12 noon also. And also here time portion is automatically set to zeros.

SELECT TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YYYY HH24:MI:SS') 
FROM dual;

Output:-

TO_CHAR(TRUNC(SYSDAT
--------------------
05-JUL-2020 00:00:00

The current time was 05-JUL-2020 at 20:15:49 but after applying the ROUND function we get 05-JUL-2020 at 00:00:00

In real-time nobody uses round() function with dates. We always use the trunc() function in the date data type.

Oracle Trunc Date Example Using Table

In oracle, whenever we are comparing dates by using the = operator within the WHERE condition then the Oracle server doesn’t return any rows because in this case, the Oracle server not only compares the date part but also compares the time part. Let us understand through an example.

Create a table and insert some date values,

CREATE TABLE test (EMPNO NUMBER(10), HIREDATE DATE);

INSERT INTO test VALUES(10, SYSDATE);
INSERT INTO test VALUES(20, '10-JAN-20');
INSERT INTO test VALUES(30, '20-FEB-19');

COMMIT;

SELECT * FROM test;

Output:-

     EMPNO HIREDATE
---------- ---------
        10 14-AUG-20
        20 10-JAN-20
        30 20-FEB-19

Task:- Write a query to display the employees who are joining today from the TEST table given above.

SELECT * FROM test WHERE HIREDATE = SYSDATE;

Output:-

no rows selected

Whenever we are comparing dates by using the = operator within the WHERE condition then the Oracle server doesn’t return any rows. To overcome this problem we must set the time portion as zero by using TRUNC() functions.

Solution:-

SELECT * FROM test WHERE TRUNC(HIREDATE) = TRUNC(SYSDATE);

We can also retrieve proper results by comparing by using the to_date function.

SELECT * FROM TEST WHERE HIREDATE = 
TO_DATE('14-08-2020 10:34:25', 'DD-MM-YYYY HH24:MI:SS');

TRUNC Date, Sysdate, Day, Month, and Year

Note:- In Oracle using ROUND(), and TRUNC() functions we can also return the first date of the year, the first date of the month, and the first day of the week.

Syntax to trunc date to month or day or year:-
TRUNC(date, 'format')

Format	Return
D / DAY	First day of the week (Sunday)
MM / MON / MONTH	First day of the month
YY / Y YYY / YEAR	First day of the year

Examples:-

To trunc year from the sysdate,

SQL> SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;

TRUNC(SYS
---------
01-JAN-20

Trunc month from the sysdate,

SQL> SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;

TRUNC(SYS
---------
01-JUL-20

Trunc day from the sysdate,

SQL> SELECT TRUNC(SYSDATE, 'DAY') FROM dual;

TRUNC(SYS
---------
05-JUL-20

It always returns the first day (Sunday) of the week.

SQL> SELECT TRUNC(TO_DATE('10-FEB-25'), 'DAY') FROM dual;

TRUNC(TO_
---------
09-FEB-25

Other Examples of trunc oracle date,

SQL> SELECT TRUNC(TO_DATE('10-FEB-25'), 'MONTH') FROM dual;

TRUNC(TO_
---------
01-FEB-25

SQL> SELECT TRUNC(TO_DATE('10-FEB-25'), 'YEAR') FROM dual;

TRUNC(TO_
---------
01-JAN-25

SQL> SELECT TRUNC(TO_DATE('10/FEB/25', 'DD/MON/YY'), 'DAY') 
FROM dual;

TRUNC(TO_
---------
09-FEB-25

Since 10/FEB/25 is not in the Oracle default format so, we have to explicitly convert it into the date using the TO_DATE() conversion function. Learn more:- to_date() in Oracle database

Example Using ROUND() with Oracle Dates

Similar to TRUNC() function with Oracle dates, we can also use the ROUND() function with Oracle dates. Examples are:-

SELECT ROUND(SYSDATE, 'YEAR') FROM dual;

Output:-

ROUND(SYS
---------
01-JAN-21

Execution:- For ROUND(date, ‘YEAR’), the Oracle server checks whether the given date is in (Jan – Jun) or in (Jul – Dec). If the date belongs to JUL – DEC then it will return the first date of the next year.

SELECT ROUND(SYSDATE, 'MONTH') FROM dual;

Output:-

ROUND(SYS
---------
01-JUL-20

Execution:- Server checks given date belongs to 1 – 15 or in 16 – 31. If the given date belongs to 1 – 15 then it will return the first date of the month else it returns the first date of the next month.

SELECT ROUND(SYSDATE, 'DAY') FROM dual;

ROUND(SYS
---------
05-JUL-20

Execution:- Server checks given date belongs to SUN–TUE or in WED-SAT. If the given date belongs to SUN – TUE then it will return the first day (Sunday) of the current week else it will return the first day (Sunday) of the next week.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• How to unlock Scott user in SQL Plus
• Error ORA 01031 insufficient privileges

Search