5 Useful SQL Date Queries

As an Oracle Technical Developer we deal with queries regarding dates often whether it is range bound, specific day, specific month or specific year. These date queries are simple but still proves to be time consuming when client is on our neck. Sometimes we feel that these queries don’t behave as they should. So in order to save some of your precious time I had listed out 5 useful SQL date queries in one place to make handy to you during stringent times. You can bookmark this page if you want. It will serve as your assistant at the times when you need date queries on the go. Also this page will serve as a source of interview specific questionnaires for job seekers or job changers.

Click to read about brief history of Oracle SQL

First and Last Date of Current Month and Year

  • First and last date of Current Month
SELECT TRUNC(SYSDATE, 'MM') 
    FROM dual;
SELECT LAST_DAY(SYSDATE) 
    FROM dual; 
  • Find out last date of Current Year
SELECT TRUNC(SYSDATE, 'RR')
    FROM dual;
SELECT LAST_DAY( ADD_MONTHS(TRUNC(SYSDATE, 'RR'), 11) ) 
    FROM dual;

Days Left and Days Passed in Current Year

Days left in current year

SELECT (ADD_MONTHS(TRUNC(SYSDATE, 'rr'), 12)-1) - 
                   TRUNC(SYSDATE) days_left_in_current_year 
    FROM dual;

Days passed in current year

SELECT TRUNC(SYSDATE) - TRUNC(SYSDATE, 'RR') days_passed_in_current_year 
    FROM dual;

Financial Year Beginning

Financial year beginning (for all the months except January)

This is example for April (4th) month which is the start of financial year for many organizations

SELECT CASE WHEN TO_NUMBER ( TO_CHAR ( SYSDATE, 'RRRRMM')) < TO_NUMBER(TO_CHAR(SYSDATE, 'RRRR')||'04') 
        THEN ADD_MONTHS(ADD_MONTHS(TRUNC ( SYSDATE, 'RR'), -12), (04-1))
       ELSE ADD_MONTHS(TRUNC ( SYSDATE, 'RR'), (04-1)) 
       END fin_year 
 FROM dual;

If your organization have financial year starting from September you have to just replace 04 with 09. Don’t forget to use ‘0’ as prefix for months having digit in single integer.

Financial year beginning (for January)

SELECT CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'RRRRMM')) < TO_NUMBER(TO_CHAR(SYSDATE,'RRRR')||'13') 
            THEN ADD_MONTHS( ADD_MONTHS(TRUNC(SYSDATE, 'RR'), -12), 13-1) 
            ELSE ADD_MONTHS(TRUNC(SYSDATE, 'RR'), 13-1) 
       END fin_year 
    FROM dual;

Remaining retirement years of an employee

SELECT TO_NUMBER(TO_CHAR(TO_DATE('01-JAN-2000'),'RR'))+60 -                  
                  TO_NUMBER(TO_CHAR(SYSDATE,'RR')) remaining_retirement_years 
    FROM dual;

if retirement years in your organization is 58 then replace 60 with 58

Determine age of an employee in years

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'RRRR')) - 
                 TO_NUMBER(TO_CHAR(TO_DATE('01-JAN-1990'),  'RRRR')) age 
    FROM dual;

If you found this post useful, then comment below.

Also you can Subscribe here to stay updated of the new posts.

Click here for official Oracle Date and Time Function Documentation.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.