Re: How to calculate Last and First Day of Month?

Well, yes the first day of the month is always 1.

Representing it as the first day of the current month requires you to use the SYSDATE function.

Returning the first day of the month can be done in several ways, some more reasonable than others.

there's the new to SQL basic programmer DBA wannabe method:

select

   substr(to_char(sysdate,'MM/DD/YYYY'), 1,2) ||    '/01/' ||
   substr(to_char(sysdate,'MM/DD/YYYY'), 7) from dual;

There's the slightly better:

select

   to_date(to_char(sysdate,'YYYYMM') || '01','YYYYMMDD') from dual;

The obtuse date math method ( my favorite ):

select

   last_day(add_months(sysdate,-1))+1
from dual;

( there are several variations on this method )

A little RTFM will reveal a much cleaner method:

select

   trunc(sysdate,'mm')
from dual;

So, the answer is always 1, but there is more than one way to get there.

Jared
SA, OCP, and Part Time Perl Evangelist

"Charlie Mengler" <charliem_at_mwh.com>
Sent by: root_at_fatcity.com
01/23/02 12:59 PM
Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: How to calculate Last and First Day of Month?

I would have thought that the "first day of the month" is ALWAYS the 1st, as in 1 (ONE)!

Please explain why it needs to be calculated or could be a value other than ONE.

>
> >>> stant_98_at_yahoo.com 01/23/02 10:35AM >>>
> Hey guys,
>
> I know this is propablby easy, but I'm a bit
> overwhelmed here this week. Can you please tell me how
> to get first and last days of the month given SYSDATE?
>
> thanks a lot
>
> Regards
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: charliem_at_mwh.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).