CODEWITHSUNDEEP
mysqlsql
user5407606
user5407606

Reputation:

sql date related query

I have the following table and one row in it. When I use this following query:

SELECT FROM ROOM WHERE chk_in = '7-APR-14';

it shows no data. Any idea where i am going wrong? 

CREATE TABLE room (
r_id   number(5),
book_date    date,
chk_in  date,
chk_out  date,
r_ct_id    number(6),
booking_id    number(5),
c_id    number(9),
primary key (r_id),
foreign key (booking_id) references booking_info(booking_id),
foreign key (c_id) references customer(c_id)
);


INSERT INTO room VALUES (61276,'4-FEB-14','7-APR-14','4-APR-14',87689,19857,987987987);

Upvotes: 0

Views: 29

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use a proper format for date. Assuming chk_in is a date with no time component:

WHERE chk_in = '2014-04-07'

If there is a time component, use an inequality:

WHERE chk_in >= '2014-04-07' and
      chk_in < date_add('2014-04-07', interval 1 day)

The inequality makes it possible for MySQL to use an index on chk_in for the query.

Upvotes: 1

Khairul Islam
Khairul Islam

Reputation: 1215

If your column type of chk_in is date then use-

SELECT FROM ROOM WHERE chk_in = STR_TO_DATE('7-APR-14', '%d/%l/%Y');

Upvotes: 2

Related Questions