asked on

ORACLE db date logic

I need to create date logic for use in a query to retrieve after-hours activity.

Mon - Fri = between 11pm and 8am the next day.
Weekends - between Sat 7pm and before 8am Monday.

I have already done the code snippet below and need an OR statement. How do I modify it to give me the day scenario above?
Thanks.

AND a.ROW_ADDED_DTTM >= TO_DATE(:1,'YYYY-MM-DD HH:MI:SS PM')
AND a.ROW_ADDED_DTTM < TO_DATE(:2,'YYYY-MM-DD HH:MI:SS AM') + 1/1440

Kent Olsen

Hi Saved,

Use the Oracle to_char to get the day of the week.

WHERE (to_char (a.ROW_ADDED_DTTM, 'D') in (1, 7) AND ...) -- weekend
OR (to_char (a.ROW_ADDED_DTTM, 'D') in (2,3,4,5,6) AND ...) -- weekday

Good Luck,
Kent

Sean Stuber

 WHERE (TO_CHAR(row_added_dttm, 'Dy') = 'Sat' AND row_added_dttm >= TRUNC(row_added_dttm) + 19 / 24) -- >= Saturday after 7pm
    OR TO_CHAR(row_added_dttm, 'Dy') = 'Sun' -- all day Sunday
--    OR (TO_CHAR(row_added_dttm, 'Dy') = 'Mon' AND row_added_dttm < TRUNC(row_added_dttm) + 8 / 24) -- < Monday 8am  (not needed, handled by clause below)
    OR (TO_CHAR(row_added_dttm, 'Dy') IN ('Mon',
                                          'Tue',
                                          'Wed',
                                          'Thu',
                                          'Fri',
                                          'Sat') 
    AND (row_add_dttm >= TRUNC(row_added_dttm) + 23 / 24
      OR row_add_dttm < TRUNC(row_added_dttm) + 8 / 24)) -- Weekdays after 11pm or before 8am of next day

Open in new window

Sean Stuber

Here's a little simpler version of the syntax that should be functionally equivalent to what you asked for (at least, as I understood it) but the clauses don't translate 1-1 with your conditions..

 WHERE row_add_dttm >= TRUNC(row_add_dttm) + 23 / 24 -- Any day after 11pm is considered after hours
    OR row_add_dttm < TRUNC(row_add_dttm) + 8 / 24 -- Any day before 8am is considered after hours
    OR (TO_CHAR(row_added_dttm, 'Dy') = 'Sat' AND row_added_dttm >= TRUNC(row_added_dttm) + 19 / 24) -- >= Saturday after 7pm
    OR TO_CHAR(row_added_dttm, 'Dy') = 'Sun' -- all day Sunday

Open in new window

saved4use

ASKER

@sdstubber This is perfect!!

Now, how do I customize the code for the period '06/01/2013' and '06/30/2013'?
Thanks.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer