Oracle Date format on where clause-CodePudding

I have to run the report with start time between 00.00 AM and 05.00 AM , but the start time field is varchar field and its has "20211110200336"

I am trying with

WHERE TO_CHAR(TO_DATE(starttime,' YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS')BETWEEN '000000' AND '050000'

but i am getting error as "ORA-01840: input value not long enough for date format 01840. 00000 - "input value not long enough for date format" *Cause: "

Can anyone help me how to use it

CodePudding user response：

You make the decision to store a DATE column as a VARCHAR2 format (which is not recomended).

Why? You can't use the provided functionality of the DATE data type and you must use your own implementation.

To get the time part (i.e. HH24MISS) from your column use a substr

select substr('20211110200336',9,6) hh from dual;

HH    
------
200336

So the predicate would be

WHERE substr(starttime,9,6) BETWEEN '000000' AND '050000'

You may transform the column to the DATE type in the first place

to_date('20211110200336','yyyymmddhh24miss')

but you should ask yourself, why did you not this choice in the DB design (??!)...

Anyway to get the time part - use to_char

select to_char(to_date('20211110200336','yyyymmddhh24miss'),'HH24MISS') hh from dual;

HH    
------
200336

CodePudding user response：

You can do it all as strings:

SELECT *
FROM   table_name
WHERE  SUBSTR(starttime, 9) BETWEEN '000000' AND '050000'

CodePudding user response：

You can use substring, so that oracle will convert it and treat as numbers

create table mytime(tm varchar2(14))

INSERT INTO mytime VALUES ('20211110200336')

1 rows affected

INSERT INTO mytime VALUES ('20211110040336')

1 rows affected

SELECT tm  FROM mytime WHERE substr(tm,-6) BETWEEN 0 AND 50000

TM
20211110040336

fiddle