13 Replies Latest reply: Apr 20, 2006 3:39 PM by ABB RSS

    Date Function

    475908
      How to find a first day of the month and last day of the month given only month and year.
      The input willbe '200603' for March 2006.
        • 1. Re: Date Function
          Warren Tolentino
          SQL> select add_months(last_day(to_date('200603','yyyymm'))+1,-1) as first_days,
            2         last_day(to_date('200603','yyyymm')) as last_days
            3    from dual;
          
          FIRST_DAY LAST_DAYS
          --------- ---------
          01-MAR-06 31-MAR-06
          
          SQL>
          • 2. Re: Date Function
            APC
            The first day of the month is easy...

            Cheers, APC
            • 3. Re: Date Function
              143269
              first day is select to_date('20060301','yyyymmdd') from dual;
              • 4. Re: Date Function
                475908
                Thanks!! Your help is greatly appreciated.
                • 5. Re: Date Function
                  John Spencer
                  Why not simply:
                  SQL> SELECT TO_DATE('200603','yyyymm') first,
                    2         LAST_DAY(TO_DATE('200603','yyyymm')) last
                    3  FROM dual;

                  FIRST     LAST
                  --------- ---------
                  01-MAR-06 31-MAR-06
                  :-)

                  TTFN
                  John
                  • 6. Re: Date Function
                    Warren Tolentino
                    or you can simplify the first day of a given month and year by
                    SQL> select trunc(to_date('200603','yyyymm')) as first_day
                      2    from dual;
                    
                    FIRST_DAY
                    ---------
                    01-MAR-06
                    
                    SQL> select to_date('200603','yyyymm') as first_day
                      2    from dual;
                    
                    FIRST_DAY
                    ---------
                    01-MAR-06
                    
                    SQL> 
                    • 7. Re: Date Function
                      Warren Tolentino
                      you're right john that is what i know of too :D
                      • 8. Re: Date Function
                        475908
                        Wrote file afiedt.buf

                        1 create or replace procedure first_last (
                        2 snap_dt number)
                        3 as
                        4 v_date date;
                        5 first_days date;
                        6 last_days date;
                        7 BEGIN
                        8 SELECT TO_DATE(to_char(snap_dt,'yyyymm')) into first_days,
                        9 LAST_DAY(TO_DATE(to_char(snap_dt,'yyyymm'))) into last_days
                        10 FROM dual;
                        11* end;
                        SQL> /

                        Warning: Procedure created with compilation errors.

                        SQL> show errors procedure first_last
                        Errors for PROCEDURE FIRST_LAST:

                        LINE/COL ERROR
                        -------- -----------------------------------------------------------------
                        8/1 PL/SQL: SQL Statement ignored
                        9/55 PL/SQL: ORA-00923: FROM keyword not found where expected
                        SQL>

                        What's wrong in this.
                        • 9. Re: Date Function
                          475908
                          GOt it, its solved.

                          Thanks.
                          • 10. Re: Date Function
                            475908
                            Wrote file afiedt.buf

                            1 create or replace procedure first_last (
                            2 snap_dt varchar2)
                            3 as
                            4 v_date date;
                            5 first_days date;
                            6 last_days date;
                            7 BEGIN
                            8 SELECT TO_DATE(to_char(snap_dt,'yyyymm')) ,
                            9 LAST_DAY(TO_DATE(to_char(snap_dt,'yyyymm'))) into first_days, last_days
                            10 FROM dual;
                            11* end;
                            SQL> /

                            Procedure created.

                            SQL> exec first_last '200603'
                            BEGIN first_last '200603'; END;

                            *
                            ERROR at line 1:
                            ORA-06550: line 1, column 18:
                            PLS-00103: Encountered the symbol "200603" when expecting one of the following:
                            := . ( @ % ;
                            The symbol ":=" was substituted for "200603" to continue.


                            Sorry again!! Encountered another problem
                            • 11. Re: Date Function
                              Jens Petersen
                              SQL> create or replace procedure first_last (snap_dt number)
                                2  as
                                3    first_days date;
                                4    last_days  date;
                                5  BEGIN
                                6    first_days := TO_DATE(snap_dt,'yyyymm');
                                7    last_days  := last_day(first_days);
                                8    dbms_output.put_line(to_char(first_days, 'DD-MON-YYYY'));
                                9    dbms_output.put_line(to_char(last_days, 'DD-MON-YYYY'));
                              10  end;
                              11
                              12  /

                              Procedure created.

                              SQL> set serveroutput on
                              SQL> exec first_last(200603);
                              01-MAR-2006
                              31-MAR-2006

                              PL/SQL procedure successfully completed.

                              SQL>
                              • 12. Re: Date Function
                                475908
                                Thanks!!
                                • 13. Re: Date Function
                                  ABB
                                  SQL> create or replace procedure first_last (snap_dt number)
                                  2  as
                                  3    first_days date;
                                  4    last_days  date;
                                  5  BEGIN
                                  6    first_days := TO_DATE(snap_dt,'yyyymm');
                                  7    last_days  := last_day(first_days);
                                  8    dbms_output.put_line(to_char(first_days,
                                  s, 'DD-MON-YYYY'));
                                  9    dbms_output.put_line(to_char(last_days,
                                  s, 'DD-MON-YYYY'));
                                  10  end;
                                  11
                                  12  /

                                  Procedure created.

                                  SQL> set serveroutput on
                                  SQL> exec first_last(200603);
                                  01-MAR-2006
                                  31-MAR-2006

                                  PL/SQL procedure successfully completed.

                                  SQL>
                                  I think you meant to use a VARCHAR2 parameter :o)