Solved

oracle date and time query

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

919 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

18 Experts available now in Live!

Get 1:1 Help Now