Solved

oracle date and time query

Posted on 2013-11-14
5
468 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
[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
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 38

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

634 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