Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle Weekends only from the last 6 months onwards

Posted on 2011-02-28
5
Medium Priority
?
479 Views
Last Modified: 2012-05-11
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
Comment
Question by:lemmohr
  • 4
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34997993
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34998031
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 34998045
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
 

Author Closing Comment

by:lemmohr
ID: 34998875
this is perfect, i forgot to mention that it is a date type
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34998887
glad I could help,  remember  dates don't have formats.

Only strings do
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question