Fetch dates between from months and years

[ketansetia]

i'm unable to fetch dates, details are as follows:-

Input parameter is - dd/mm/yyyy

Table data - Column Month and Year.

Current query is

select * from table_name tn where
(tn.month > '10' and tn.year = '2009') or
(tn.year = '2009' and tn.month <'12'))

// problem is that this will fetch duplicate records as months <'12' will also return duplicate data.

How to implement between here in columns where months and year are seperate.

Please advise.

[PAVB]

Don't even want to know why a date is stored as separate varchar2() columns but this may help you in describe scenario...

SQL> 
SQL> create table table_name(
  2  month varchar2(2),
  3  year  varchar2(4)
  4  );

Table created.

SQL> insert into table_name values ('09','2009');

1 row created.

SQL> insert into table_name values ('10','2009');

1 row created.

SQL> insert into table_name values ('11','2009');

1 row created.

SQL> insert into table_name values ('12','2009');

1 row created.

SQL> insert into table_name values ('01','2010');

1 row created.

SQL> commit;

Commit complete.

SQL> select  *
  2  from    table_name tn
  3  where   to_date(tn.month||tn.year,'mmyyyy') 
  4          between to_date('102009','mmyyyy') 
  5              and to_date('122009','mmyyyy')
  6  ;

MO YEAR
-- ----
10 2009
11 2009
12 2009

SQL>

Please take into consideration "between" returns both ends of the range.

[ketansetia]

Thanks Paul,

But still problem presists, as the table column for month and year are of type numbers..

ORA-01843: not a valid month is coming.

How to cast this here.

[PAVB]

But still problem presists

I see... you didn't tell us everything on first post, don't you ))

as the table column for month and year are of type numbers..

Use to_char() to convert them from number to chars... be sure you format them correctly, two chars for month, four chars for year.

ORA-01843: not a valid month is coming.

If wrongly formatted data, format it correctly.
If invalid data, fix it.