About between date range query

Sushant Torankar

3y1.4kOracle

Hello,

I want to get records between date range, using Oracle-PL/Sql.

Following is the query:

Select * From [Table_Name]

where (to_date(P_End_Date, 'DD/MM/YYYY') between to_date(FromDate, 'DD/MM/YYYY') and to_date(ToDate, 'DD/MM/YYYY'));

P_End_Date, FromDate and ToDate are of Varchar datatype

I am getting error in query as ORA - 01843:Not a valid month

P_End_Date column has Hypen(-) or Slash(/) in some records in my table, will it affect error in query ?

Thank you

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana3y ago

Hi Sushant,

You can create a function to convert string to different formate like below:

create or replace function clean_date
    ( p_date_str in varchar2)
    return date
is
    l_dt_fmt_nt sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll
        ('DD-MM-RR', 'MM-DD-RR', 'RR-MM-DD', 'RR-DD-MM'
         , 'DD-MM-YYYY', 'MM-DD-YYYY', 'YYYY-MM-DD', 'YYYY-DD-MM');
    return_value date;
begin
    for idx in l_dt_fmt_nt.first()..l_dt_fmt_nt.last()
    loop
        begin
            return_value := to_date(p_date_str, l_dt_fmt_nt(idx));
            exit;
        exception
             when others then null;
        end;
    end loop;
    if return_value is null then
        raise no_data_found; 
    end if;
    return return_value;
exception
    when no_data_found then
        raise_application_error(-20000, p_date_str|| ' is unknown date format');
end clean_date;
/

then you can use this function in your query:

select  clean_date('20160817') from dual;

Reference : https://stackoverflow.com/questions/43776687/oracle-convert-many-date-formats-to-a-single-formatted-date

Thanks

Naimish

Sushant Torankar3y ago
Thank you Muhammad Imran Ansari for a reply.

I tried following solution, but still it I'm getting same error.

Maybe my date contains other than Slash or Hypen.

Any other to fix it ?

Thank you
Muhammad Imran Ansari3y ago
Hi Sushant,

The error "ORA-01843: not a valid month" is usually caused by a mismatch between the date format specified in the query and the actual date format in the data.

if some of the records in the P_End_Date column contain a slash (/) or a hyphen (-) instead of a forward-slash (/) as the date separator, the conversion will fail and the query will return the ORA-01843 error.

One way to solve this problem is to use the 'replace' function to replace any slashes or hyphens with forward-slashes before converting the strings to dates.
```
Select * From [Table_Name]
where (to_date(replace(P_End_Date, '-','/'), 'DD/MM/YYYY') between to_date(replace(FromDate, '-','/'), 'DD/MM/YYYY') and to_date(replace(ToDate, '-','/'), 'DD/MM/YYYY'));
```