Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- REFERENCE WEBSITE: http://www.vertabelo.com/blog/technical-articles/the-most-useful-date-and-time-functions-in-oracle-database
-- ADD MONTHS
SELECT ADD_MONTHS(SYSDATE, 1) AS NEWMONTHS FROM DUAL;
-- LAST_DAY OF THE MONTH IN DD-MON-YY FORMAT FOR THE DATE SPECIFIED IN ARGUMENT
SELECT LAST_DAY(SYSDATE) AS "Last" FROM DUAL;
SELECT MONTHS_BETWEEN(SYSDATE,CURRENT_DATE) FROM DUAL;
-- SELECT SECOND ARGUMENT MATCHING WEEKDAY THAT COMES IMMEDIATELY AFTER THE FIRST ARGUMENT DATE
SELECT NEXT_DAY(TO_DATE(SYSDATE,'DD:MM;YY'), 'MONDAY' ) FROM DUAL;
-- <FIRST ARGUMENT DATE >, <SECOND ARG DATE>
-- ROUND() AND TRUNC() FUNCTIONS
SELECT ROUND(CURRENT_DATE, 'YEAR') FROM DUAL;
SELECT TRUNC(CURRENT_DATE, 'YEAR') FROM DUAL;
-- ORA-00975: date + date not allowed
SELECT TO_DATE(CURRENT_DATE)+TO_DATE('2010-05-06', 'YYYY-MM-DD') FROM DUAL;
-- returns NUMBER OF DAYS IN BETWEEN
SELECT TO_DATE('2010-05-05', 'YYYY-MM-DD')-TO_DATE('2010-05-06', 'YYYY-MM-DD') FROM DUAL; -- -1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment