Wednesday, October 2, 2013

Get the First & Last Day of The Month In Oracle

Oracle figures if we can get the last day of the month using its handy LAST_DAY function, we should be able to figure out the first day, right?

Yep, here is one of many ways to get the first and last day of the month in Oracle.

Select  (last_day(:1)  - TO_CHAR(last_day(:1), 'DD')) + 1 as First_Day, last_day(:1) as last_Day from dual

Replace bind :1 to the actual date, then you get the result below:


  1. if my fetched date is 9/22/2016 how to make the date value to 10/1/2016

  2. Hi Charan,

    Sorry for my delay response. I did not see your comment earlier.

    I am not sure what you are trying to accomplish from your example above. It looks like to me that could be easily accomplished by using a case statement in your SQL.