Solved

oracle date and time query

Posted on 2013-11-14
5
460 Views
Last Modified: 2013-11-15
start time - 3/1/2011 11:00:00 AM

end time - 2/1/2009 3:00:00 PM

I want to have a if statement like
 

If start_time <= 8:00 AM then

 ........do this

elsif end_time >= 7:00 PM then

  ......do this

End if;

How can I do it?
0
Comment
Question by:anumoses
5 Comments
 
LVL 32

Accepted Solution

by:
awking00 earned 250 total points
ID: 39648731
Assuming the datatypes are dates -
if start_time - trunc(start_time) <= 8/24 then ...
elsif end_time - trunc(end_time) >= 19/24 then ...
end if;
0
 
LVL 35

Expert Comment

by:YZlat
ID: 39648911
or


IF TO_NUMBER(TO_CHAR(start_time,'HH24')) <= 8 THEN
   
ELSIF TO_NUMBER(TO_CHAR(end_time,'HH24')) >= 19 THEN
0
 
LVL 23

Expert Comment

by:David
ID: 39648924
There are functions, of course, to correctly parse and compare the dates / times -- TO_DATE(), TO_CHAR(), and TRUNC() are heavily documented here and elsewhere.

While awking's pseudo-code is correct, I'm curious if you should be asking a better question.  What do your business rules dictate, for example, if the start_time is 10:00 -- or in a different time zone -- or if the period were to extend into another day?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39650418
is this the start end of a shift ?
comparing the start -end time of people entering/leaving to the start/end time of the shift ?
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39650996
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

785 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