Oracle SQL select date with timestamp

Question

I have the following data   SQL gt  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 query that selects all records that matches a certain date  however I am using timestamp for the BK DATE field and no matter what I try it my query yields no results   SQL  I have tried the following queries but they yield no results  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 whole date value like   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

User · Answer

You can specify the whole day by doing a range, like so:

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 parameter removes hours, minutes and seconds from a DATE.

User · Answer

Answer provided by  Nicholas Krasnov  SELECT   FROM BOOKING SESSION WHERE TO CHAR T SESSION DATETIME   DD-MM-YYYY     20-03-2012

[sql] Oracle: SQL select date with timestamp

Examples related to sql

Examples related to oracle