How To Display Dates Relative To Current Day

It doesn’t happen very often that DBAs need to write reports based on dates, but when they do, they might need all kind of funky dates to use, that are relative to the current date.

That is why, I put together an “emergency” list of finding dates relative to today’s date, or any other date.

You could use these dates in select statement’s where condition, where you need to return data from the current month, or current year and so on. The applications of these date is endless.

Bellow is the list I am covering today, let me know if you’d like to see more!

1. First Day Of The Current Month
2. Last Day Of The Current Month
3. First Day Of The Next Month
4. Last Day Of The Previous Month
5. First Day Of The Current Year
6. Last Day Of The Current Year

1. First Day Of The Current Month

If today is November 6 2018, what is the first day of this month? I am expecting the query to return November 1 2018.
How do you do it? One of the methods is to use the TRUNC function, by truncating the month.

SQL> select sysdate, 
            trunc(sysdate,'MM') first_day_month 
     from dual;

SYSDATE         FIRST_DAY_MONTH
--------------- ---------------
06-NOV-18       01-NOV-18

SQL> select to_date('15-AUG-18','DD-MON-YY') as day, 
            trunc(to_date('15-AUG-18','DD-MON-YY'),'MM') first_day_month 
     from dual;
DAY             FIRST_DAY_MONTH
--------------- ---------------
15-AUG-18       01-AUG-18

2. Last Day Of The Current Month

What is the last day of the current month?

SQL> select sysdate, 
	    last_day(sysdate) last_day_month 
     from dual;

SYSDATE         LAST_DAY_MONTH
--------------- ---------------
06-NOV-18       30-NOV-18

3. First Day Of The Next Month

SQL> select sysdate, 
            last_day(sysdate)+1 first_day_next_month 
     from dual;

SYSDATE         FIRST_DAY_NEXT_MONTH
--------------- --------------------
06-NOV-18       01-DEC-18

4. Last Day Of The Previous Month

SQL> select sysdate, 
            trunc(sysdate,'MM')-1 last_day_prev_month 
     from dual;

SYSDATE         LAST_DAY_PREV_MONTH
--------------- --------------------
06-NOV-18       31-OCT-18

5. First Day Of The Current Year

SQL> select sysdate,
            trunc(sysdate,'YY') first_day_year 
     from dual;

SYSDATE         FIRST_DAY_YEAR
--------------- ---------------
06-NOV-18       01-JAN-18

6. Last Day Of The Current Year

SQL> select sysdate, 
            add_months(trunc(sysdate,'YY'),12)-1 last_day_year 
     from dual;

SYSDATE         LAST_DAY_YEAR
--------------- ----------------
06-NOV-18       31-DEC-18

7. Constructing A Date From The Year

You read this far, great! I’ll give you a bonus. If you are given only the year ie. 2017, can you build the first day of the year?

SQL> select trunc(to_date('2017','YYYY'),'YY') first_day_year from dual;

FIRST_DAY_YEAR
------------------
01-JAN-17

You got a good list of date operations. Go ahead and save them, bookmark them, use them when needed!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana