asked on

How to find Weekend, AfterHours, and Normal details based on date column

I'm using Oracle 10G:

Category: ‘Weekend’ is 19:45 EST Friday to 7:00 EST Monday
‘After Hours’ is 19:45 EST to 7:00 EST next day on Mon, Tue, Wed, Thu
Otherwise is ‘Normal’

I'm using Datefield (occurreddate) which is VARCHAR datatype

lcohan

Is this what you need?

select cast(convert(date, getdate() ,101) as sysname) +' - ' + cast(DATENAME(weekday ,getdate()) as sysname)

ASKER CERTIFIED SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Talitha30

ASKER

This is what I used to get the answer I needed:

CASE
When To_Char(To_date(hp.Receiveddate, 'yyyy-mm-dd hh24:mi:ss'), 'Day') IN ('Friday', 'Monday') and to_number(to_char(To_date(hp.Receiveddate, 'yyyy-mm-dd hh24:mi:ss'),'HH24')) >= 20 OR to_number(to_char(To_date(hp.Receiveddate, 'yyyy-mm-dd hh24:mi:ss'),'HH24')) < 7 then 'Weekend'
When To_Char(To_date(hp.Receiveddate, 'yyyy-mm-dd hh24:mi:ss'), 'Day') IN ('Monday', 'Thursday') and to_number(to_char(To_date(hp.Receiveddate, 'yyyy-mm-dd hh24:mi:ss'),'HH24')) >= 20 OR to_number(to_char(To_date(hp.Receiveddate, 'yyyy-mm-dd hh24:mi:ss'),'HH24')) < 7 then 'After Hours'
Else 'Normal' end category

Ryan McCauley

I think you're just doing the same thing in your case that I'm doing in the one I posted earlier, but in a more condensed (and I think harder to read) format. Was there something wrong with the snippet I provided?