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

Quote:

---

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

---

Quote:

---

Originally Posted by gandolf989

Looks kind on complex. Wouldn't LPAD work better? ;)

---

Much better indeed or at least, much more elegant.