Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Unanswered: Last_day and First_Day of the month

    Hi,

    Can I use Last_day(table_date) and first day? I have the query below and it is not working. If not is there any suggestions? I error is pointing at the "First_day".

    SQL> SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'), 'MM/DD/YYYY') START_DATE,
    2 to_char(to_date(last_day(s.key_date),'YYYYMMDD'),' MM/DD/YYYY') END_DATE
    3 FROM schedule s;
    SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'), 'MM/DD/YYYY') START_DATE,
    *
    ERROR at line 1:
    ORA-00904: invalid column name

    Thanks a lot
    Mo

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Last_day and First_Day of the month

    There is an Oracle function called LAST_DAY that returns last day of month for given date, but not FIRST_DAY. For first day of month use TRUNC(s.key_date,'MM')

  3. #3
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    What about formatting it like this ('MM/DD/YYYY'), remeber the actual table date is in 8 bytes varchar2.

    Thanks
    Mo

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    My first reponse to that is: "why?" Why would anyone store dates as VARCHAR2 and not as DATE? Does not compute...

    Anyway, you must convert the VARCHAR2 to a DATE before applying any other functions that work with dates:

    LAST_DAY( TO_DATE(s.key_date,'MM/DD/YYYY') )

    TRUNC( TO_DATE(s.key_date,'MM/DD/YYYY'), 'MM' )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •