Solved

Oracle Weekends only from the last 6 months onwards

Posted on 2011-02-28
5
471 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 73

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 73

Accepted Solution

by:
sdstuber earned 500 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 73

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 73

Expert Comment

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

Only strings do
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now