asked on

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');

johnsone

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.

chaitu chaitu

ASKER

'mmddyyyyhh:mi:ss pm'

can if i use am instead of pm?

slightwv (䄆 Netminder)

>> 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.

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial