Understanding Dates in MySql

Question

I seem to be missing something fundamental. I an a new refugee from an old esoteric French database system (4D) and Im new to mySQL.

Given The following DB:

CREATE  TABLE `stuff` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Product` VARCHAR(45) NULL ,
  `Sell_by` DATE NULL ,
  PRIMARY KEY (`ID`) );


INSERT INTO `Stuff` (`Product`, `Sell_by`) VALUES ('Milk', '2013-05-16');
INSERT INTO `Stuff` (`Product`, `Sell_by`) VALUES ('Cheese', '2013-06-15');
INSERT INTO `Stuff` (`Product`, `Sell_by`) VALUES ('Yogurt', '2013-07-02');
INSERT INTO `Stuff` (`Product`, `Sell_by`) VALUES ('Bread', '2013-08-17');
INSERT INTO `Stuff` (`Product`, `Sell_by`) VALUES ('Twinkies', '2099-04-16');

http://sqlfiddle.com/#!2/3ef48e/1

Why doesn't this return Milk Cheese and Yogurt? It returns nothing.

SELECT * FROM  Stuff Where Sell_by <= 2013-07-04;

But this returns everything?

SELECT * FROM  Stuff Where Sell_by >= 2013-07-04;

Curt · Accepted Answer · 2013-07-02 19:31:40Z

7

Your query is not actually checking for a date, but a numeric expression ( 2013 - 7 - 4 ).

Put the date expression in single quotes ('2013-07-14') and you should be fine.

answered Jul 2, 2013 at 19:31

Curt

5,7511 gold badge25 silver badges36 bronze badges

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

1 Comment

JVMX Over a year ago

ooh. Interesting. In line math functions. I should have left that archaic database system sooner. Thanks.

DwB · Accepted Answer · 2013-07-02 19:34:13Z

5

I think you are missing quotes; try this:

SELECT * FROM  Stuff Where Sell_by <= '2013-07-04';

edited Jul 2, 2013 at 19:34

DwB

38.6k11 gold badges62 silver badges86 bronze badges

answered Jul 2, 2013 at 19:32

Arslaan Ejaz

1,00113 silver badges32 bronze badges

Comments

PiLHA · Accepted Answer · 2013-07-02 19:32:43Z

3

Use quotes around date like:

SELECT * FROM  Stuff Where Sell_by <= '2013-07-04';

answered Jul 2, 2013 at 19:32

PiLHA

2,3841 gold badge25 silver badges32 bronze badges

Collectives™ on Stack Overflow

Understanding Dates in MySql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related