CODEWITHSUNDEEP
sqlsql-server
Massey
Massey

Reputation: 1125

How to find a time stamp is morning, afternoon or evening in SQL

I have to find out whether a timestamp in a table is morning, afternoon or evening. Currently, I have the following code:

case 
   when datepart(hour, o.timestamp) between 5 and 12 
      then 'Morning' 
   when datepart(hour, o.timestamp) between 13 and 17 
      then 'Afternoon' 
   when datepart(hour, o.timestamp) > 17 
      then 'Evening' 
end

The problem with the above code is that 2018-08-03 17:30:00.000 comes out as afternoon instead of the evening. That's because the hour in 2018-08-03 17:30:00.000 is 17 so it's evaluated as the afternoon. Instead, I want to include the minute part also which will then become evening.

Upvotes: 2

Views: 6125

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

How about time comparisons?

(case when convert(time, o.timestamp) >= '05:00:00' and
           convert(time, o.timestamp) < '12:00:00'
      then 'morning'
      when convert(time, o.timestamp) >= '12:00:00' and
           convert(time, o.timestamp) < '17:00:00'
      then 'afternoon'
      else 'evening'
 end)

Note that your logic doesn't account for hours between midnight and 5 a.m.

You can do something similar with the hour itself, but I think times will be less confusing for you to work with. Also, I don't know if the boundary for morning is really noon or 1:00 p.m. Your query suggests that the boundary is 1:00 p.m. Common sense suggests noon. That is easily adjusted in the case expression.

Upvotes: 3

Related Questions