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

# Date Function

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
``````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
The first day of the month is easy...

Cheers, APC
• ###### 3. Re: Date Function
first day is select to_date('20060301','yyyymmdd') from dual;
• ###### 4. Re: Date Function
Thanks!! Your help is greatly appreciated.
• ###### 5. Re: Date Function
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
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
you're right john that is what i know of too :D
• ###### 8. Re: Date Function
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
SQL>

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

Thanks.
• ###### 10. Re: Date Function
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
```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
Thanks!!
• ###### 13. Re: Date Function
```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)