Solved

oracle date and time query

Posted on 2013-11-14
5
455 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 31

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 36

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.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

706 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

20 Experts available now in Live!

Get 1:1 Help Now