Oracle default date format DD-MON-RR (14-MAY-11) for English Language Database. DD/MM/YYYY(14/05/2011) for Turkish. You can only subtraction between two dates. The result is day count.
ROUND and TRUNC for Date
ROUND with ‘MONTH’ => If the day is 16 or more than 16, the date rolls next month and the day makes 1.
ROUND with ‘YEAR’ => If the month is 7 or more than 7, the date rolls next year and the date makes 01-JAN.
TRUNC with ‘MONTH’ => The day makes 01 and the month does not change.
TRUNC with ‘YEAR’ => The day and the month make 01-JAN and the year does not change.
sysdate : 20-JUL-03
ROUND(sysdate,’MONTH’) = 01-AUG-03
ROUND(sysdate,’YEAR’) = 01-JAN-04
TRUNC(sysdate,’MONTH’) = 01-JUL-03
TRUNC(sysdate,’YEAR’) = 01-JAN-03
ADD_MONTHS
You can increase or decrease month with add_months function.
sysdate : 10-APR-08
select add_months(sysdate,1) from dual;
Result : 10-MAY-08
or
select add_months(’15-MAY-11′,-1) from dual;Result : 15-APR-11
TO_DATE
If you do not know date format, you can use to_date to convert to the format you want. For example; default date format ‘DD-MON-YYYY’ for hire_date column. But you can to_date for ‘dd-mm-yyyy’ date format.
select * from employees where hire_date = to_date(’01-04-2011′ , ‘dd-mm-yyyy’);