Ask Question
Help Others
Home › SQL & PL/SQL

Find the First Day or Last Day of a Specific Month

Tue, 06/17/2008 - 3:27pm — Lepa
No votes yet

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;

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
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.
Wed, 08/27/2008 - 9:06am
#1
Lepa's picture
Lepa
User offline. Last seen 3 weeks 20 hours ago. Offline
Joined: 06/23/2008
Posts: 565
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

Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.