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

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA Image
Play CAPTCHA Audio
Reload Image