Oracle SQL String to Number conversion

Question

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.

Alex Poole · Accepted Answer · 2015-04-21 22:15:26Z

1

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;

edited Apr 21, 2015 at 22:15

answered Apr 21, 2015 at 21:56

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ponder Stibbons · Accepted Answer · 2015-04-21 22:08:01Z

1

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

edited Apr 21, 2015 at 22:08

answered Apr 21, 2015 at 21:58

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Collectives™ on Stack Overflow

Oracle SQL String to Number conversion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related