DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: playing with months

  1. #1
    Join Date
    Mar 2010
    Posts
    6

    playing with months

    Hi i want search between dates

    currently im using this query, but since the months in the input and the columns in the db are not consistent. for example - from input we have 01 to 12 but in db columns we have 1,2 to 12.

    Now we are unable to use to_date correctly, currently using this query

    select * from t_table t where
    to_date(t.month||t.Year, 'mmyyyy') between
    (to_date('102009','mmyyyy')) and (to_date('122009','mmyyyy'))
    and t.address_id=10;

    -- error is ora-01843 not a valid month

    Since the records in the table are 1,2,3 etc i cannot use 'mm' here, so how to use it in a generic way as i should work for all.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ...and the columns on your table are of the number datatype, init?

    How about using decode()? like decode(t.month,1,'01',2,'02',...)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by PAVB View Post
    ...and the columns on your table are of the number datatype, init?

    How about using decode()? like decode(t.month,1,'01',2,'02',...)
    Looks kind on complex. Wouldn't LPAD work better?

    Code:
    SYS AS SYSDBA> select lpad('1', 2, '0') from dual;
    
    LPAD('
    ------
    01

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by gandolf989 View Post
    Looks kind on complex. Wouldn't LPAD work better?
    Much better indeed or at least, much more elegant.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width