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

# 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
• 4
1 Solution

Commented:
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

Commented:
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

Commented:
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 Commented:
this is perfect, i forgot to mention that it is a date type
0

Commented:
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.