CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Dustin Gambino
Dustin Gambino

Reputation: 60

Oracle Developer - Hour Between 1 and 12 error, works on one machine but not another

I am running into an issue where i have oracle developer on 2 machines, the same code runs successful on one but gives an "hour must be between 1 and 12" error on the other, same versions of Oracle on both. The code snippet is below. Not sure why this would present differently, I should also note, on the machine it is failing on, is the machine i developed this on a year+ ago, and it worked fine then.

The objective of this item is based on when the script is run, if its after 0400, it runs for a date parameter of todays date >0400 for the time. if its before 0400 when run, it runs for yesterdays date >0400 until.

and cast(scn_time as timestamp) >= case when to_char(current_timestamp, 'HH24:MI:SS') 
> ('04:00:00') then to_timestamp(to_char(trunc(sysdate)||' 04.00.00 AM')) else 
to_timestamp(to_char(trunc(sysdate-1)||'  4.00.00 AM')) end

Any help would be appreciated.

Upvotes: 0

Views: 116

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

As @Abra hinted, your two SQL Developer installations have different NLS_TIMESTAMP_FORMAT settings (In Tool->Preferences->Database->NLS). You shouldn't rely on NLS settings, or implicit conversions, as your currently are when you convert the truncated date to a string. You are also relying on your session time zone when you use CURRENT_TIMESTAMP - that could get different values depending on session settings too.

You don't need to convert to or from strings here, you can do:

and scn_time >= case
  when extract(hour from current_timestamp) >= 4 
    then trunc(sysdate) + interval '4' hour 
  else 
    then trunc(sysdate) - interval '1' day + interval '4' hour 
    -- or: then trunc(sysdate) - interval '20' hour 
  end

or

and scn_time >= trunc(sysdate) + interval '1' hour * case
  when extract(hour from current_timestamp) >= 4 then 4
  else -20
  end

or

and (
  (extract(hour from current_timestamp) >= 4
    and scn_time >= trunc(sysdate) + interval '4' hour)
  or
  (extract(hour from current_timestamp) < 4
    and scn_time >= trunc(sysdate) - interval '20' hour)
 )

This also makes it easier to add an upper limit:

and (
  (extract(hour from current_timestamp) >= 4
    and scn_time >= trunc(sysdate) + interval '4' hour)
  or
  (extract(hour from current_timestamp) < 4
    and scn_time >= trunc(sysdate) - interval '20' hour)
    and scn_time < trunc(sysdate) + interval '4' hour)
 )

Casting scn_time to TIMESTAMP probably isn't necessary, and applying a function to a table column will usually prevent any index on it (except an FBI) from being used. If it's already a date then leave it alone and just make the right-hand side of the comparison a date too.

I've left CURRENT_TIMESTAMP in there for now, but you might want SYSTIMESTAMP (which isn't affected by your session settings); though it depends what data type scn_time actually is and what it represents. If that is recording the SCN time for tracking then it might be a timestamp already; in which case cast the right-hand side to the same data type. If it's a string then it shouldn't be, but if you're stuck with it then convert it to a date explicitly with to_date() and the appropriate format mask.

Upvotes: 2

Related Questions