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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!