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
Talitha30Asked:
Who is Participating?
 
Ryan McCauleyData and Analytics ManagerCommented:
I think you're going to want something like this - it looks a bit ridiculous, but seems to get the job done (though it's based on whatever the local time is - if you're dealing with multiple timezones, you'll have to take that into account as well):

select occureddate, 
	case when to_char(to_date(occurreddate), 'D') = 1 then 'Weekend'
	     when to_char(to_date(occurreddate), 'D') = 2 then 
			case when to_char(to_date'occurreddate), 'HH24:MI') <= '07:00' then 'Weekend'
			     when to_char(to_date(occurreddate), 'HH24:MI') >=  '19:45' then 'After-Hours'
				 else 'Normal'
			end
	     when to_char(to_date(occurreddate), 'D') IN (3, 4, 5) then 
			case when to_char(to_date(occurreddate), 'HH24:MI') <= '07:00' then 'After-Hours'
			     when to_char(to_date(occurreddate), 'HH24:MI') >=  '19:45' then 'After-Hours'
				 else 'Normal'
			end
	     when to_char(to_date(occurreddate), 'D') = 6 then 
			case when to_char(to_date(occurreddate), 'HH24:MI') >= '19:45' then 'Weekend'
			     when to_char(to_date(occurreddate), 'HH24:MI') <=  '07:00' then 'After-Hours'
				 else 'Normal'
			end
	     when to_char(to_date(occurreddate), 'D') = 7 then 'Weekend'
	end as TimeType
 from YourTable

Open in new window

0
 
lcohanDatabase AnalystCommented:
Is this what you need?

select cast(convert(date, getdate() ,101) as sysname) +' - ' + cast(DATENAME(weekday ,getdate()) as sysname)
0
 
Talitha30Author Commented:
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
0
 
Ryan McCauleyData and Analytics ManagerCommented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.