fetch the records based on specific timings

how to fetch the records from today 04.00 a.m to next day 04:30 a.m. please note that date and time are in separate columns so need to append and fetch the records.

Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (1,to_date('19-MAY-15','DD-MON-RR'),'12:12:00 pm');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (2,to_date('19-MAY-15','DD-MON-RR'),'6:45:00 pm');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (3,to_date('20-MAY-15','DD-MON-RR'),'06:45:00 am');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (4,to_date('20-MAY-15','DD-MON-RR'),'04:29:33 am');
LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
The disclaimer is that this will only work if your data in the time field is always correct.  There can be no errors in it.
SELECT * 
FROM   employee 
WHERE  To_date(To_char(employee_date, 'mmddyyyy') 
               ||start_time, 'mmddyyyyhh:mi:ss pm') BETWEEN 
       Trunc(SYSDATE) + ( 4 / 24 ) AND Trunc(SYSDATE) + ( 28.5 / 24 ); 

Open in new window

If there are issues with the time field, this query will likely error out.
0
chaitu chaituAuthor Commented:
'mmddyyyyhh:mi:ss pm'

can  if i use am instead of pm?
0
slightwv (䄆 Netminder) Commented:
>> can  if i use am instead of pm?

That is just the format mask that tells Oracle how to convert the string into a date.

The documentation for the format masks:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00212

The answer to your question is:  Yes but am or pm in the mask doesn't affect the actual date values.
0
Mark GeerlingsDatabase AdministratorCommented:
For best performance with Oracle queries, you should always avoid applying an operator (like; the "to_char" in the example from johnsone) on database columns in query "where" clauses.  I suggest writing your query more like this:
 

This also has the advantage of not failing with an "invalid date" format error, if there is data in the table that is not formatted correctly.
SELECT * 
FROM   employee 
WHERE  (employee_date = Trunc(SYSDATE)
  and start_time between '04:00:00 am' and '12:59:59 pm')
 or (employee_date = Trunc(SYSDATE) +1
  and start_time between '00:00:00 am' and '04:30:00 am'
  and start_time not like '%pm')

Open in new window

0
johnsoneSenior Oracle DBACommented:
I don't like character comparisons like that.  Introduces a lot of possible errors if the data is bad.  I'd rather get data errors than do string comparison.

If you are worried about indexing, then I would use:
SELECT * 
FROM   employee 
WHERE  employee_date >= Trunc(SYSDATE) 
       AND employee_date < Trunc(SYSDATE) + 2 
       AND To_date(To_char(employee_date, 'mmddyyyy') 
                   ||start_time, 'mmddyyyyhh:mi:ss pm') BETWEEN 
           Trunc(SYSDATE) + ( 4 / 24 ) AND Trunc(SYSDATE) + ( 28.5 / 24 ); 

Open in new window

Duplicates some of the conditions, but gets the range that is scanned down to the 2 day range.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.