Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Timezone Validation

789216Aug 3 2010 — edited Aug 3 2010
Hi All

I need to check Australia day not in (sat,sun) and time between 8 am - 8 pm for below query

SQL> SELECT present.object_type, present.object_id
2 FROM (SELECT cb2.object_type, cb2.object_id,incident_status_name,incident_severity_name
3 FROM cs_brm_3d_service_request_v cb2,
4 cs_incidents_all cia,
5 jtf_rs_groups_vl jrgv
6 WHERE cb2.record_status = 'PRESENT'
7 AND ( cb2.incident_id = cia.incident_id )
8 AND cb2.incident_type_name = 'Customer Service Request'
9 AND cia.owner_group_id = jrgv.GROUP_ID
10 AND jrgv.group_name IN ('CSA')
11 AND cb2.incident_status_name NOT IN
12 ('Waiting on Client',
13 'Pending Research',
14 'Waiting on Responder')
15 AND cb2.close_date IS NULL
16 AND cb2.creation_date <= (SYSDATE - 1440 / 1440)
17 AND NOT EXISTS (SELECT 1 FROM cs_hz_sr_contact_points chsc
18 WHERE chsc.incident_id =cb2.incident_id)
19 )present
20 WHERE 1=1
21 AND TO_CHAR (SYSDATE, 'dy') NOT IN ('sat', 'sun')
22 AND SYSDATE >= TRUNC(SYSDATE) + (8 / 24)
23 AND SYSDATE <= TRUNC(SYSDATE) + (20 / 24);

Could you please Advise me..

My server in New york
This post has been answered by Frank Kulash on Aug 3 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 31 2010
Added on Aug 3 2010
2 comments
724 views