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'))
-- 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',...)
Looks kind on complex. Wouldn't LPAD work better? ;)
Originally Posted by PAVB
SYS AS SYSDBA> select lpad('1', 2, '0') from dual;
Much better indeed or at least, much more elegant.
Originally Posted by gandolf989