playing with months

[ketansetia]

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.

[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',...)

[gandolf989]

...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? ;)


SYS AS SYSDBA> select lpad('1', 2, '0') from dual;

LPAD('
------
01

[PAVB]

Looks kind on complex. Wouldn't LPAD work better? ;)
Much better indeed or at least, much more elegant.