oracle date and time query

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?
LVL 6
anumosesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
awking00Connect With a Mentor Commented:
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
 
YZlatCommented:
or


IF TO_NUMBER(TO_CHAR(start_time,'HH24')) <= 8 THEN
   
ELSIF TO_NUMBER(TO_CHAR(end_time,'HH24')) >= 19 THEN
0
 
DavidSenior Oracle Database AdministratorCommented:
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
 
Geert GOracle dbaCommented:
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
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.