Solved

oracle query

Posted on 2014-01-06
3
281 Views
Last Modified: 2014-01-06
I want to capture all expired frozen platelets products after 47 hours and 45 minutes from expiry_date

ie process_dats <= expiry_date + 47 hours and 45 mins.

Help appreciated.
script.txt
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 125 total points
ID: 39759206
Try this:

select [whatever you need]
from [your_table]
where process_dats <= (expiry_date + 1 + 95/96)

or:
where process_dats <= (expiry_date + 191/96)

But, if you need to find expired records, and if they expire after two days (48 hours), then you may need to use ">=" (greater than or equla instaed of less than or equal) like this:

where process_dats >= (expiry_date + 1 + 95/96)

Date arithmetic explanation:  you  can add fractional portions of a day when you need Oracle to calculate or derive a date value.  For example, if you need to find all records that are almost one day (23 hours) old, you can do this:

where [date_column] >= sysdate + 23/24

In your case, where you need quarter-hour intervals, multiple the number of hours in a day by 4 (24 x4 = 96) to get the denominator to work with.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
ID: 39759401
you can also use interval math with explicit units

process_dats <= expiry_date + (interval '47' hour) + (interval '45' minute)
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39759449
thanks a lot
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

696 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