CODEWITHSUNDEEP
sqloracle-database
james_weasel
james_weasel

Reputation: 25

Parameters on time stamps in oracle sql developer

select TO_CHAR(date1,'HH24:MI:SS') time_in,
CASE 
  WHEN TO_CHAR(date1,'HH24:MI:SS') between '6:00:00' AND '18:00:00' then 'day_shift'
  WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '18:00:00' AND '24:00:00' then 'night_shift' 
  WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '0:00:00' AND '6:00:00' then 'night_shift'
  END AS shift
from blah

Trying to make it so you can see what shift it is based off a time stamp I am pulling from a table in sql developer. Any suggestions or help?

Upvotes: 0

Views: 119

Answers (2)

Alex Poole
Alex Poole

Reputation: 191455

Since between is inclusive, your query currently attempts to match 18:00:00 in both the first two when clauses, and 06:00:00 in the first and third. It will stop on the first match anyway, but having the same value in two clauses makes it unclear what you expect or want to happen. You also won't ever get a value of '24:00:00', even with leap seconds.

So you can simplify that a bit to:

select to_char(date1,'HH24:MI:SS') time_in,
  case when to_char(date1,'HH24:MI:SS') between '06:00:00' and '18:00:00'
    then 'day_shift'
    else 'night_shift'
  end as shift
from blah;

It appears that your the ends of your ranges are probably one second beyond what you need. If the first range really only needs to end earlier than 18:00 then that should be:

select to_char(date1,'HH24:MI:SS') time_in,
  case when to_char(date1,'HH24:MI:SS') between '06:00:00' and '17:59:59'
    then 'day_shift'
    else 'night_shift'
  end as shift
from blah;

Although you only really to look at the hour in that case - you might still prefer to have the full time to make it obvious what you're looking at though. And if the date is a timestamp you could even do:

select to_char(date1,'HH24:MI:SS') time_in,
  case when extract(hour from date1) between 6 and 17
    then 'day_shift'
    else 'night_shift'
  end as shift
from blah;

And if it is a date not a timestamp you can still do that if you cast, but it may not be worth the effort:

select to_char(date1,'HH24:MI:SS') time_in,
  case when extract(hour from cast(date1 as timestamp)) between 6 and 17
    then 'day_shift'
    else 'night_shift'
  end as shift
from blah;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

HH24 creates zero-padded hours. You might try:

select TO_CHAR(date1,'HH24:MI:SS') time_in,
       (CASE WHEN TO_CHAR(date1,'HH24:MI:SS') between '06:00:00' AND '18:00:00'
             THEN 'day_shift'
             WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '18:00:00' AND '24:00:00'           
             THEN 'night_shift' 
             WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '00:00:00' AND '06:00:00'          
             THEN 'night_shift'
        END) AS shift
from blah

Upvotes: 1

Related Questions