Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

Oracle Weekends only from the last 6 months onwards

Let's say I have a table that has 2 coloumns:

TicketNumber
DateOpened - (YYYY-MM-DD HH24:MI:SS format)

I would like to get only the tickets opened during the weekends (From Friday at 19:00:00 to Monday at 07:00:00) from 6 months ago up to the last weekend. this would be ran every monday morning at 7:30am

what would be the optimized way of doing this?

thanks!
0
lemmohr
Asked:
lemmohr
  • 4
1 Solution
 
sdstuberCommented:
DateOpened - (YYYY-MM-DD HH24:MI:SS format)


does that mean dateopened is actually a string type rather than a date type

or just that you prefer to see date types converted to strings in that format?
0
 
sdstuberCommented:
assuming it really is a date then...



SELECT *
  FROM yourtable
 WHERE (TO_CHAR(dateopened, 'Dy') = 'Fri' AND dateopened >= TRUNC(dateopened) + 19 / 24)
       OR (TO_CHAR(dateopened, 'Dy') = 'Mon' AND dateopened <= TRUNC(dateopened) + 7 / 24)
       OR (TO_CHAR(dateopened, 'Dy') IN ('Sat', 'Sun'))
          AND dateopened >= ADD_MONTHS(TRUNC(SYSDATE, 'mm') - 6)
0
 
sdstuberCommented:
if it is a string type then, do the same thing, but convert it to a date first


SELECT *
  FROM yourtable
 WHERE (TO_CHAR(TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss'), 'Dy') = 'Fri'
        AND TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss') >=
                TRUNC(TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss')) + 19 / 24)
       OR (TO_CHAR(TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss'), 'Dy') = 'Mon'
           AND TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss') <=
                   TRUNC(TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss')) + 7 / 24)
       OR (TO_CHAR(TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss'), 'Dy') IN ('Sat', 'Sun'))
          AND TO_DATE(dateopened, 'yyyy-mm-dd hh24:mi:ss') >= ADD_MONTHS(TRUNC(SYSDATE, 'mm') - 6)
0
 
lemmohrAuthor Commented:
this is perfect, i forgot to mention that it is a date type
0
 
sdstuberCommented:
glad I could help,  remember  dates don't have formats.

Only strings do
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now