-
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.
-
...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.
-
Originally Posted by PAVB
...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
-
Originally Posted by gandolf989
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|