shouldn't your in_time timestamp mask be 'hh24:MI:ss' since you're only passing in in_time=10:00:00
You have several implicit conversions between varchar2, date and timestamp in you expression.
I think you will find this example more stable:
with dt(in_date,in_time,pos_date) as (select '2019-08-29','22:00:00',systimestamp from dual) Select pos_date,in_date,in_time from dt where pos_date between to_timestamp(in_date,'YYYY-MM-DD') and to_timestamp(in_date || ' ' || in_time,'YYYY-MM-DD hh24:MI:ss') POS_DATE IN_DATE IN_TIME ----------------------------------- ---------- -------- 29-AUG-19 20:17:50,383161000 +02:00 2019-08-29 22:00:00
Edit: Sorry Jeff, I didn't mean to respond to you.
and i was going down the same road as you with the suggestion, i just got distracted, so thanks!
I think I have found the problem. When running sqldeveloper I have NLS_LANG=SWEDEN_SWEDISH.UTF8 Via ORDS I get AMERICAN_AMERICA.UTF8 because the owner of the weblogic installation has that set in the profile. I can reproduce the error in sqldeveloper by setting nls-lang with alter session. Is there anywhere E.g in default.xml that I can configure NLS_LANG for ORDS?
I know I have added too many conversions but that was just a desperate try to find some code that worked also via ORDS.
Is there anywhere E.g in default.xml that I can configure NLS_LANG for ORDS?
Don't rely on NLS settings. Since you can reproduce the error now you can give the code I've posted a try
The mask is refering to in_date piped together with in_time.
I tried it and it worked :-)