sql date related query

Question

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

I can't even run your CREATE table statement. Inserting '4-FEB-14' gives me error because MySQL couldn't recognize this dateformat. — Tin
– Tin, Commented Apr 18, 2016 at 21:12

Khairul Islam · Accepted Answer · 2016-04-18 21:06:55Z

2

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

answered Apr 18, 2016 at 21:06

Khairul Islam

1,2151 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-04-18 21:08:49Z

1

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.

answered Apr 18, 2016 at 21:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

sql date related query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related