CODEWITHSUNDEEP
sqloracle-database
Craig
Craig

Reputation: 47

Oracle SQL String to Number conversion

I have a table where the column type is varchar2, and holds data such as '22:00' or "13:30". I'm trying to compare that number to the current time, and if the current time is before that time, select the data.

Here is my current code:

select DISTINCT BBT_JOURNEYSTOPS.SERVICE "Service", BBT_JOURNEYSTOPS.STOP_REFERENCE "StopNo", 
  STOP_NAME "Near", BBT_STOPS.ROAD_NAME "On", BBT_JOURNEYSTOPS.JOURNEYTIME "Duration"

from BBT_JOURNEYSTOPS
inner join BBT_WEEKLYSCHEDULE
on BBT_WEEKLYSCHEDULE.SERVICE = BBT_JOURNEYSTOPS.SERVICE
inner join BBT_STOPS
on BBT_JOURNEYSTOPS.STOP_REFERENCE = BBT_STOPS.STOPREF
where  (UPPER(BBT_JOURNEYSTOPS.SERVICE) LIKE UPPER('%'|| :route || '%')) AND 
  (TO_NUMBER(SUBSTR(BBT_WEEKLYSCHEDULE.TIMEUNTIL, 1, 2)) > (SELECT TO_NUMBER(SYSDATE, 'HH24') "NOW" FROM DUAL));

It's the WHERE statement that is having the issues. The error code I get is

ORA-01481: invalid number format model
01481. 00000 -  "invalid number format model"
*Cause:    The user is attempting to either convert a number to a string
           via TO_CHAR or a string to a number via TO_NUMBER and has
           supplied an invalid number format model parameter.
*Action:   Consult your manual.

Upvotes: 0

Views: 1561

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You can't directly call TO_NUMBER with a date. You have an implicit TO_CHAR happening, and then you're trying to explictly convert that string to a number using a date format model, HH24, which is what's giving that error. You don't need the subquery against DUAL either.

Instead of:

... > (SELECT TO_NUMBER(SYSDATE, 'HH24') FROM DUAL));

you would use:

... > TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')));

You can also EXTRACT(HOUR FROM SYSDATE). Alternatively you could convert your time string to a date object using today's date and compare more exactly, with something like:

... AND TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD') || ' ' || TIMEUNTIL,
  'YYYY-MM-DD HH24:MI') > SYSDATE;

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

I think that condition should be:

SUBSTR(BBT_WEEKLYSCHEDULE.TIMEUNTIL, 1, 2) > to_char(sysdate, 'HH24')

This part of your filter causes error: 

SELECT TO_NUMBER(SYSDATE, 'HH24') "NOW" FROM DUAL

Upvotes: 1

Related Questions