JAR JAR - 4 months ago 9
SQL Question

MySQL - get date column from a table

I have a MySQL db with a MappingTable which consists of two columns. First column is a

date
column and another is ID -
Autoincrement
int column. I created this table for mapping dates and the ID's. When I query the date column with dates to retrieve the ID, no rows are getting selected. Any reason?

I tried


  1. date_format
    in the SELECT query

  2. str_to_date
    while checking in the WHERE clause

  3. Compared like
    current_date > "2016-07-12"
    AND
    current_date <= "2016-07-12"



IfI compare
LIKE "2016-07-1%"
I'm getting matching rows but if I select
"2016-07-12%"
though there are matching rows, it is giving 0 rows.

I defined my column as
DATE
only.

Anything I'm missing here?

CREATE TABLE `mapping_table` (
`Current_date` date DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;


My question is, I want to select something like this.

select id from mapping_table where current_date="2016-07-12";


I tried with all approaches as mentioned above, but no rows are not retrieving.

Answer

use back tick on columns and table names so it wont be read/parse as keyword.

 select `id` from `mapping_table` where `current_date` = "2016-07-12";