Printable View
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.
Don't even want to know why a date is stored as separate varchar2() columns but this may help you in describe scenario...
Please take into consideration "between" returns both ends of the range.Code: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>
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.
I see... you didn't tell us everything on first post, don't you :)))Quote:
Originally Posted by ketansetia
Use to_char() to convert them from number to chars... be sure you format them correctly, two chars for month, four chars for year.Quote:
Originally Posted by ketansetia
If wrongly formatted data, format it correctly.Quote:
Originally Posted by ketansetia
If invalid data, fix it.