Find the First Day or Last Day of a Specific Month

Your rating: None Average: 1 (1 vote)

First Day of a Month
An SQL that takes a date value such as 01/15/2008 and returns the first day of that month. In our example, the first day for Jan of 2008 is Tuesday.

SELECT TO_CHAR (TRUNC (TO_DATE (:1, 'mm-dd-yyyy'), 'MM'), 'DAY')
  FROM DUAL;

The TRUNC function in the above example will truncate the passed date value to be always the begging of the moth and the TO_CHAR function with 'DAY' will return the day (Tuesday).

Last Day of a Month
Fortunately Oracle has the LAST_DAY function to do that. Here is an SQL on how to use it.

SELECT TO_CHAR (LAST_DAY (TO_DATE (:1, 'mm/dd/yyyy')), 'DAY')
  FROM DUAL;

If you are looking for the last "date" in a month then:
SELECT LAST_DAY (TO_DATE (:1, 'mm/dd/yyyy'))
  FROM DUAL;

Or you can directly pass in an actual date value:
SELECT LAST_DAY (SYSDATE)
  FROM DUAL;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Lepa's picture
User offline. Last seen 48 weeks 2 days ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Find the First Day or Last Day of a Specific Month

I was asked a question on how could we get the last day of the month if we only know the month (like 07 for july) and not the whole date? and here is how:

--when prompted for a value, pass in your month as a number.  For example 07 for july
SELECT LAST_DAY (TO_DATE (:2, 'MM'))
  FROM DUAL;

LAST_DAY(TO_DATE(:2,'MM'))
7/31/2008

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog