Oracle Peoplesoft Corner: Get the First & Last Day of The Month In Oracle

Wednesday, October 2, 2013

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:

UnknownSeptember 22, 2016 at 7:18 AM
if my fetched date is 9/22/2016 how to make the date value to 10/1/2016
ReplyDelete
Replies
Eliando Kentjana PutraOctober 11, 2016 at 9:43 AM
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.

Cheers,

Edo
ReplyDelete
Replies