Useful SQL Queries – Date and Time
Useful SQL Queries for Oracle DB – Date & Time
1. How to get number of days between two dates?
SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0) num_of_days FROM DUAL;
2. How Get the last day of the month?
SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month" FROM DUAL;
3. How to get the first day of a month?
SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL;
4. How to get the first day of the Year?
SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;
5. How to get the last day of the year?
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL