setting time range in SQL Developer

I am working on a dataset that contains car accidents and their time of occurrence. (the data set exists in SQL Server under the name accident).

I have a column that is in date format. I would like to extract the time from the column. Then add a new column called lightining_period label the time as daytime or nighttime. My problem is with setting a range for the times, as I get the wrong label each time I run the code.

Different lighting periods (daytime: 6AM – 5:59PM and nighttime 6PM – 5:59AM).

[1]First I wrote this code to extract the time from accident_date_time and store it in a new column time.

create table lightiningPeriod as 
select to_char(accident_date_time,'HH:MMAM') as time
from accident.accident;

[2]Then, I altered the table to add the column lightining_label where I want to store the labels daytime/nighttime.

alter table lightiningPeriod add (
lightining_label varchar2(20)
);

[3]Finally, I used Update statement to change the values of lightining_label according to time ranges. But the tables come out wrong. I tried using between, to_date, cast, convert but none of them worked.

update lightiningPeriod
set lightining_label='daytime'
where time >= '06:00AM'
and time <= '5:59PM';

below is a sample of the output I get which shows wrong labels.

time    lightining_label
06:04AM daytime
11:04AM daytime
01:04AM (null)
10:04AM daytime
10:04AM daytime
04:04PM (null)
07:04PM daytime
01:04PM (null)

Answer

Thank you all, I have solved the query doing this:

update lightiningPeriod
set lightining_label='daytime'
where to_char(accident_date_time, 'hh24:mi') >= '06:00'
and to_char(accident_date_time, 'hh24:mi') <= '17:59';

Advertisement

Answer