Using MOD, (TRUNC & ROUND FUNCTION with DATE) in PLSQL Query

https://randomsrsolutions.blogspot.com/2018/05/blog-post.html

select ENAME, SAL,
MOD(SAL,500),
SAL-MOD(SAL,500),
(SAL-MOD(SAL,500))/500 NOTES_500,
ROUND((SAL-MOD(SAL,500))/1000) NOTES_1000,
TRUNC((SAL-MOD(SAL,500))/1000) NOTES_1000_2
from EMP
where ENAME = 'KING';


ENAME SAL MOD(SAL,500) SAL-MOD(SAL,500) NOTES_500 NOTES_1000 NOTES_1000_2
KING     5000               0                            5000                    10                     5                         5

------------------------------------------------------------------------------------------------------------

Round(MONTH)
01-15     FIRST DAY OF SAME MONTH
16-31     FIRST DAY OF NEXT MONTH

ROUND(YEAR)
01-JAN ... 30-JUN     FIRST DAY OF THE SAME YEAR
01-JUL ... 31-DEC     FIRST DAY OF THE NEXT YEAR

TRUNC(MONTH)
01-31     FIRST DAY OF SAME MONTH

TRUNC(YEAR)
01-JAN ... 31-DEC     FIRST DAY OF SAME YEAR

ROUND(DD) DEFAULT IF NOT SPECIFIED
LESS THAN 12:00PM SAME DAY
MORE THAN 12:00PM NEXT DAY

ROUND(DAY)
BEFORE 12:00PM WEDNESDAY, PREVIOUS SUNDAY OF THE WEEK
AFTER 12:00PM WEDNESDAY, NEXT SUNDAY OF THE WEEK

TRUNC(DD)   SAME DAY, ELIMINATING TIME PORTION FROM DATE
TRUNC(DAY) PREVIOUS SUNDAY

-------------------------------------------------------------------------------------

Assume SYSDATE is 05-MAY-2018

select SYSDATE-10,
ROUND(SYSDATE-10,'MONTH'),
TRUNC(SYSDATE-10,'MONTH')
from Dual;

SYSDATE-10       ROUND(SYSDATE-10,'MONTH')       TRUNC(SYSDATE-10,'MONTH')
25-APR-18                   01-MAY-18                                    01-APR-18



select SYSDATE,
ROUND(SYSDATE,'YEAR'),
TRUNC(SYSDATE,'YEAR')
from dual;

SYSDATE        ROUND(SYSDATE,'YEAR')      TRUNC(SYSDATE,'YEAR')
05-MAY-18                     01-JAN-18                                   01-JAN-18


select SYSDATE,
ROUND(SYSDATE-10,'YEAR'),
TRUNC(SYSDATE-10,'YEAR')
from dual;

SYSDATE          ROUND(SYSDATE-10,'YEAR')          TRUNC(SYSDATE-10,'YEAR')
05-MAY-18                       01-JAN-18                                               01-JAN-18


select SYSDATE,
NEXT_DAY(SYSDATE,'SUNDAY') COMING_SUNDAY,
ROUND(SYSDATE,'DAY') COMING_SUNDAY,
TRUNC(SYSDATE,'DAY')   LAST_SUNDAY
from DUAL;

SYSDATE      COMING_SUNDAY    COMING_SUNDAY    LAST_SUNDAY
05-MAY-18          06-MAY-18                    06-MAY-18               29-APR-18         


Suppose Date is 05-MAY-2018 and time is 10:11PM  

select SYSDATE, ROUND(SYSDATE), TRUNC(SYSDATE) from dual;

SYSDATE       ROUND(SYSDATE)       TRUNC(SYSDATE)
05-MAY-18          06-MAY-18                        05-MAY-18


Note: Oracle also store time with date. When comparing date column without time portion, it is recommended to use trunc() function with that column. Because trunc() eliminate time portion. Remember TRUNC(DD) remove time portion and TRUNC(DD) is default.

select HIREDATE from EMP where trunc(HIREDATE) = '17-DEC-1980'

HIREDATE
17-DEC-80