CODEWITHSUNDEEP
mysqlsqldatetime
Trace
Trace

Reputation: 18889

SQL date format and quoting confusion

I have the following test record:

enter image description here

The following query results in 0 records: 

SELECT * FROM `events_dates` WHERE `start_date` = 21-12-2014

But the following query results in 1 record: 

SELECT * FROM `events_dates` WHERE `start_date` > 21-12-2014

I'm a bit confused about that.

As a side question: is it ok to use the 'date' type if I will not use the time field, or would you still rather choose to use 'datetime'?

Upvotes: 3

Views: 835

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270677

You have to single-quote the date literals in the format 'YYYY-MM-DD'. Otherwise, what MySQL is interpreting is an arithmetic expresssion (integer subtraction):

21 - 12 - 2014 = -2005

The negative integer -2005 is cast to a date value of effectively 0000-00-00 00:00:00, which explains why your query with > returns a row.

The correct SQL expression would be:

SELECT * FROM `events_dates` WHERE `start_date` = '2014-12-21'

Regarding the use of the DATE type instead of DATETIME, yes, I would consider it okay to use it without the time portion if you know you never intend to use times. I would admit though, that most of the time, I do use DATETIME for future expandability. It really depends on your future needs.

If the concern is optimization of storage space to avoid storing the time with DATETIME, consider how much potential this table has to grow very large before worrying about optimizing it (it'd have to be really huge to matter). If you do use DATETIME instead, you may find yourself frequently using the DATE() function to truncate the values at query-time. I consider that to be only a very minor inconvenience.

The same query if start_date was a DATETIME column can use DATE() to truncate off the time segment:

SELECT * FROM `events_dates` WHERE DATE(`start_date`) = '2014-12-21'

Upvotes: 7

Girish
Girish

Reputation: 12127

MySql date field support only YYYY-MM-DD date format, this query give you correct result 

SELECT * FROM `events_dates` WHERE `start_date` > '2014-12-21'

Although quote date field with (" or ')

Upvotes: 1

Related Questions