Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: last day function

    TAB1 has columns, year and month. Year and month are number fields.
    I need to get the last day of the month from the month and year fields in tab1----
    How do I write the last_day function. I wrote something like this:
    select last_day(to_date('month'||'year','mm/yyyy'))
    from tab1

    I initially got this error:
    ERROR at line 1:
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    But now I'm getting this error:
    ORA-01858: a non-numeric character was found where a numeric was expected
    (for month)
    SQL> select year, month from tab1;

    YEAR MONTH
    ----------- -----------------
    2006 10
    2006 10
    2006 10
    2006 10
    2006 10
    2006 10
    2006 10
    2006 10
    2006 10
    2006 10
    2006 9

    YEAR MONTH
    ----------- -----------------
    2006 9
    2006 9
    2006 9

    14 rows selected.

    SQL> desc TAB1;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    PROJECT_ID NOT NULL VARCHAR2(15)
    CATEGORY NOT NULL VARCHAR2(5)
    AMOUNT NOT NULL NUMBER(26,3)
    OPRID NOT NULL VARCHAR2(30)
    YEAR NOT NULL NUMBER(38)
    MONTH NOT NULL NUMBER(38)
    SEGMENT NOT NULL VARCHAR2(10)
    I have changed the actual column names to month and year

    Can someone help me with this?
    Last edited by nandinir; 11-09-06 at 15:58.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    select substr(year, 1,4)
    from tab1
    where length(year) = 4;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    SQL> ed
    Wrote file afiedt.buf

    1* select last_day(to_date('10'||'2006','mm/yyyy')) from dual
    SQL> /

    LAST_DAY(
    ---------
    31-OCT-06
    It works fine here then why not in my query?

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Perphaps you should remove the quotes from the columns ?

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    SQL> ed
    Wrote file afiedt.buf

    1 select substr(year, 1,4)
    2 from tab1
    3* where length(year) = 4;
    SQL> /
    where length(year) = 4;
    *
    ERROR at line 3:
    ORA-00911: invalid character
    year is a number field as you can see the description on the table above.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by JMartinez
    Perphaps you should remove the quotes from the columns ?
    good point
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Maybe because you had the column names wrapped in single QUOTES?
    PHP Code:
    SELECT LAST_DAY(TO_DATE(MONTH||YEAR,'MM/YYYY'))
      
    FROM TAB1 

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by nandinir
    year is a number field as you can see the description on the table above.
    I fail to see the relevance. Are you saying a number has no length?

    PHP Code:
    sqlcreate table temp01 (a number);

    Table created.

    sqlinsert into temp01 values (1234);

    1 row created.

    sqlcommit;

    Commit complete.

    sqlselect length(afrom temp01;

     
    LENGTH(A)
    ----------
             

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    Number definitely has length. I was thinking that there must be some type conversion problem.

    I removed the quote too, but still get the same error.
    SQL> ed
    Wrote file afiedt.buf

    1* select last_day(to_date(month||year,'mm/yyyy')) from tab1
    SQL> /
    ERROR:
    ORA-01843: not a valid month
    It works fine when I substitute the value:
    SQL> ed
    Wrote file afiedt.buf

    1* select last_day(to_date(10||2006,'mm/yyyy')) from tab1
    SQL> /

    LAST_DAY(
    ---------
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06
    31-OCT-06

    LAST_DAY(
    ---------
    31-OCT-06
    31-OCT-06
    31-OCT-06

    14 rows selected.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You may need to convert to character:
    PHP Code:
    SELECT LAST_DAY(TO_DATE(TO_CHAR(MONTH*10000+YEAR,'FM000000'),'MMYYYY')) 
      
    FROM TAB1 

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Jul 2005
    Posts
    276
    Now I get it....the month value for the previous months is 6,7,8 and
    not 06,07 and so when i say 'mm' for month its not reading it and gives
    'Not a valid month' error.

    select last_day(to_date(month||year,'mm/yyyy')) from tab1

    For testing purpose, I deleted the rows which has month as 'm' and then it worked fine.
    Now i need to figure out for data which has month as 'm' .There is huge data and I cant change it perhaps to 'MM'. Can anyone suggest something.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    1. month||year <> 'mm/yyyy'
    your concatenation will never equal your date conversion

    2. here you go, basically if the length is 5 then add a zero.
    PHP Code:
    sys@dwbatselect to_char(month||yearfrom temp02;

    TO_CHAR(MONTH||YEAR)
    --------------------------------------------------------------------------------
    12005

    sys
    @dwbatselect
      
    case when length(dt) = 5 then
            last_day
    (to_date('0'||dt'mmyyyy'))
           else
            
    last_day(to_date(dt'mmyyyy'))
      
    end the_date
    from
      
    (select to_char(month||yeardt from temp02);

      
    2    3    4    5    6    7    8
    THE_DATE
    -----------
    31-JAN-2005 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I admit, I didn't carefully read this thread, but CASE could be avoided using the LPAD function:
    Code:
    SELECT LAST_DAY (TO_DATE (LPAD (TO_CHAR (MONTH || YEAR), 6, '0'), 'mmyyyy')
                    ) last
      FROM TEMP02;

  14. #14
    Join Date
    Jul 2005
    Posts
    276
    Thank you so much Littlefoot and Duck. That helps a lot.

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by Littlefoot
    I admit, I didn't carefully read this thread, but CASE could be avoided using the LPAD function:
    Code:
    SELECT LAST_DAY (TO_DATE (LPAD (TO_CHAR (MONTH || YEAR), 6, '0'), 'mmyyyy')
                    ) last
      FROM TEMP02;
    lpad would be better. nice one
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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