SQL Date Functions

Date Functions

SQL Functions:-Oracle has provided a lot of functions that are divided in below sub-category
            A) Single row functions    
                1) Numeric Functions
                2) String Functions
                3) Date Functions
                4) Conversion Functions
            B) Multi row/Group/Grouping function

Date function :-Below are the listed function under Date Functions category.
sysdate,last_day(date),add_months(date,n),next_date(date,'day'),months_between(date1,date2)

SQL>select sysdate from dual;
SQL>select last_day(sysdate) from dual;--return last date of that month
SQL>select last_day('08-JUL-2014') from dual;
SQL>select add_months(sysdate,4) from dual;
SQL>select add_months(sysdate,-8) from dual;
SQL>select NEXT_DAY(SYSDATE, 'FRIDAY') from dual;
SQL>select NEXT_DAY('08-JUL-2014', 'FRIDAY') from dual;
SQL>select months_between('02-FEB-2014','08-JUL-2012') from dual;

Note :-Trunc function works differently on date value

SQL>select sysdate from dual;
SQL>select trunc(sysdate,'MM') from dual; --Trunc to Month (It will return 1st date of that month)
SQL>select trunc(to_date('12-07-2004','dd-mm-yyyy'),'MM') from dual;
SQL>select trunc(to_date('12072004','mmddyyyy'),'MM') from dual;

SQL>select trunc(sysdate,'YYYY') from dual; --Trunc to Year (It will return 1st date of that Year)
SQL>select trunc(to_date('12-07-2004','dd-mm-yyyy'),'YYYY') from dual;
SQL>select trunc(to_date('12072004','mmddyyyy'),'YYYY') from dual;