3

Is Oracle (10g) doing a proper TIME comparison here, or do I need to convert to decimal time and then compare? E.g.,

IF (SELECT TO_CHAR(sysdate,'HH24:MI:SS') from dual) <= '15:00'
THEN (...)

Thanks.

APC
  • 144,005
  • 19
  • 170
  • 281
vincent
  • 1,305
  • 2
  • 12
  • 16
  • 1
    This just compares two strings. Does that even execute? It is neither SQL nor PL/SQL. Try something like this: `IF SYSDATE <= TRUNC(SYSDATE) + (15/24) THEN ...` – The Nail Mar 08 '12 at 21:37
  • If you mean "the time must be *on or before* 3pm" then you need "<= '15:00:00'". If you mean "the time must be *before* 3pm" then you could have done "TO_CHAR(sysdate,'HH24') < '15'" – David Aldridge May 23 '13 at 13:24

3 Answers3

10
IF (sysdate <= trunc(sysdate)+15/24)
THEN (...)

should do the trick...

cagcowboy
  • 30,012
  • 11
  • 69
  • 93
  • +1 Got in there first with of what was to be the last part my answer! – Ben Mar 08 '12 at 21:47
  • 5/8 would be even better (says the math major) ;-) – David Faber Mar 09 '12 at 00:36
  • @DavidFaber, it's less obvious what you're doing though. I always use modular arithmetic mod-24 - to maths it up slightly :-), when doing hour calculations as it's very clear what's going on. – Ben Mar 09 '12 at 08:16
  • Does trunc(sysdate) effectively create a time of "0", to which 15/24 is added, thus making a time of 15? – vincent Mar 09 '12 at 14:29
  • 1
    Yes. TRUNC(date) "rounds-down" the date to midnight (00:00:00). Adding 15/24 is adding 15 hours, thus taking you to 15:00:00. – cagcowboy Mar 09 '12 at 15:25
4

You can't do a select in an if statement, but you can do a direct comparison to sysdate. If you're doing it like this it would probably be better to use a number rather than relying on implicit conversion. You also don't need the extra minutes etc. Something like, 

begin

   if to_number(to_char(sysdate,'HH24')) <= 15 then
      -- do something
   end if;

end;

If you did want to use the minutes then by converting it into a string without the colon you can do a more direct comparison. As long as the date / time is converted in 24 hour format without extras and in reverse, year, month, day, hour etc comparisons will always be accurate, e.g.

begin

   if to_char(sysdate,'HH24MI') <= '1515' then
      -- do something
   end if;

end;

However, it's often best to do date comparisons as @cagcowboy has just posted before I got there!

Ben
  • 51,770
  • 36
  • 127
  • 149
1

use the following code

SELECT TO_CHAR(SYSDATE, 'HH24MI') INTO V_SYS_TIME FROM DUAL;

IF V_SYS_TIME BETWEEN V1_TIME AND V2_TIME THEN 
(....)
Tabish
  • 1,592
  • 16
  • 13