Practice DATE FUNCTIONS

>>>Previous>>>

DATE FUNCTIONS:

ADD_MONTHS(date, n) : Adds n months to the specified date .

SQL> SELECT ADD_MONTHS('1-JAN-05',5) FROM DUAL;

ADD_MONTHS
------------------
01-JUN-05

LAST_DAY(date): Gives last date of the specified month (date).

SQL> SELECT LAST_DAY('1-JAN-05') FROM DUAL;

LAST_DAY(
---------
31-JAN-05

MONTHS_BETWEEN(date1, date2): It gives difference between the two dates date1, date2 in months.

SQL> SELECT MONTHS_BETWEEN('31-DEC-05','1-JAN-05') FROM DUAL

MONTHS_BETWEEN('31-DEC-05','1-JAN-05')
--------------------------------------
11.9677419

SQL> SELECT MONTHS_BETWEEN('31-JUL-05','1-JUL-05') FROM DUAL

MONTHS_BETWEEN('31-JUL-05','1-JUL-05')
--------------------------------------
.967741935

NEXT_DAY(date, ‘day’ ) : It gives date of the next occurrence of the specified day after the given date.

SQL> SELECT NEXT_DAY('01-JAN-05','FRI') FROM DUAL;

NEXT_DAY( (Next Friday after 1-jan-05 is on 7-jan-05)
---------
07-JAN-05

TO_DATE (string): This function converts a string into an Oracle date.

SQL> SELECT TO_DATE('01 JANUARY 2005','DD MONTH YYYY') FROM DUAL;

TO_DATE('
---------
01-JAN-05

SQL> SELECT TO_DATE('MAR 05 01','MON YY DD') FROM DUAL;

TO_DATE('
---------
01-MAR-05

SQL> SELECT TO_DATE('01/01/05', 'DD/MM/YY') FROM DUAL;

TO_DATE('
---------
01-JAN-05

>>>Next>>>