CODEWITHSUNDEEP
sqloracle-databasetime
Mirwais Faizi
Mirwais Faizi

Reputation: 17

ORA-01722: invalid number #time

I am running the below mentioned query in my Oracle and I am getting ORA-01722: invalid number #time.

  select *
  from time_checker
  where to_char(time,'HH24:MI')
    not between to_char('01:00','HH24:MI') and to_char('02:30','HH24:MI');

Upvotes: 0

Views: 225

Answers (2)

CS_noob
CS_noob

Reputation: 557

The correct version of the query is

select * from time_checker where time not between 
  to_date('01:00','HH24:MI') and to_date('02:30','HH24:MI');

Here time is the field you are checking. So ideally you must convert the parameters '01:00' etc to the datatype of the column time.

If time column is of type timestamp, then use to_timestamp.

Upvotes: 1

Anatoly Samoylenko
Anatoly Samoylenko

Reputation: 701

to_char (n, fmt) converts n to a value of VARCHAR2 datatype, using the format fmt: docs.

Maybe you should use to_date.

Upvotes: 0

Related Questions