Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle: SQL select date with timestamp

Tags:

sql

oracle

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 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
);
like image 462
David Garcia Avatar asked Nov 12 '12 02:11

David Garcia


People also ask

How do I select a date from a TIMESTAMP in SQL?

In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column.

How do I display a TIMESTAMP in SQL?

The CURRENT_TIMESTAMP function returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format. Tip: Also look at the GETDATE() function.

Can we compare date with TIMESTAMP in Oracle?

You can use the TO_DATE function. Convert both to timestamp and compare. This solution handles nulls as greatest will return null if one of the params is null.

How do I select date in Oracle?

SELECT * FROM SDE. fuse_h WHERE DATECREATED between to_date('18/01/2002','DD/MM/YYYY') and to_date('18/01/2002 23:59:59','DD/MM/YYYY HH24:MI:SS'); this will allow any index to be used that exists on the datecreated column too. Remember, always use 4 digit years when specifying dates.


1 Answers

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.

like image 70
n00b Avatar answered Oct 10 '22 03:10

n00b