Oracle: SQL select date with timestamp

Question

I have the following data:

SQL> select * from booking_session; BK_ID|BK_DATE -----|------------------------- 1|18-MAR-12 10.00.00.000000 2|18-MAR-12 10.25.00.000000 3|18-MAR-12 10.30.00.000000 4|18-MAR-12 10.35.00.000000 5|18-MAR-12 10.40.00.000000 

I am trying to write a sql that selects all records matching a specific date, however I am using a timestamp for the BK_DATE field, and no matter what I try to do, my query gives no results.

SQL : I tried the following queries but failed

1.

 select * from booking_session where bk_date = to_date('18-03-2012', 'dd-mm-yyyy'); 

2.

 select * from booking_session where bk_date = to_timestamp('18-03-2012', 'dd-mm-yyyy'); 

3.

 select * from booking_session where bk_date = to_date('18-MAR-2012', 'dd-mm-yyyy'); 

It only works when I set the value of the entire date, for example:

 select * from booking_session WHERE bk_date = '18-MAR-12 11.00.00.000000'; CREATE TABLE BOOKING_SESSION( BK_ID NUMBER NOT NULL, BK_DATE TIMESTAMP, BK_BOOKER NUMBER, BK_CUSTOMER NUMBER, BK_TREATMENT NUMBER, T_SESSION_DATETIME TIMESTAMP, STAFFAPPOINTED NUMBER, BK_ROOM NUMBER ); INSERT INTO BOOKING_SESSION VALUES ( 1, TO_TIMESTAMP('18/03/2012 10:00', 'DD/MM/YYYY HH24:MI'), 1, 1, 1, TO_TIMESTAMP('20/03/2012 11:00', 'DD/MM/YYYY HH24:MI'),2,1 ); 

Answer 1

Answer by Nikolay Krasnov

 SELECT * FROM BOOKING_SESSION WHERE TO_CHAR(T_SESSION_DATETIME, 'DD-MM-YYYY') ='20-03-2012'; 

Answer 2

You can specify all day by running a range, for example:

 WHERE bk_date >= TO_DATE('2012-03-18', 'YYYY-MM-DD') AND bk_date < TO_DATE('2012-03-19', 'YYYY-MM-DD') 

More simply, you can use TRUNC:

 WHERE TRUNC(bk_date) = TO_DATE('2012-03-18', 'YYYY-MM-DD') 

TRUNC without a parameter deletes hours, minutes and seconds from DATE.