Solutions:
1. Maintaining the current table structure:
Format the DataCadastro column within the where
clause to compare two DATE
values:
mysql> SELECT * FROM cad_clientes WHERE DATE_FORMAT(DataCadastro, '%Y-%m-%d') = CURDATE();
+----+---------------------+
| id | DataCadastro |
+----+---------------------+
| 1 | 2016-03-02 18:27:17 |
| 2 | 2016-03-02 00:00:00 |
+----+---------------------+
2. Changing the table structure:
Caution: Only use this solution if you are sure that you do not need the times in the registers in the DataCadastro field. Once you change the type of the field in the table, the times will be lost!
mysql> ALTER TABLE cad_clientes MODIFY DataCadastro DATE;
all records:
mysql> SELECT * FROM cad_clientes;
+----+--------------+
| id | DataCadastro |
+----+--------------+
| 1 | 2016-03-02 |
| 2 | 2016-03-02 |
+----+--------------+
and only the records with your filter:
mysql> SELECT * FROM cad_clientes WHERE DataCadastro = CURDATE();
+----+--------------+
| id | DataCadastro |
+----+--------------+
| 1 | 2016-03-02 |
| 2 | 2016-03-02 |
+----+--------------+
Explanation:
Your problem is probably because your DataCadastro field is of type DATETIME
, that is, date + time , and you try to make a comparison with the value returned by function CURDATE()
, which returns only a date in the format 'YYYY-MM-DD'
.
Your search would give sure that your records always had the value of DataCadastro with time zeroed, type 2016-03-02 00:00:00
, but when the column value has some time set , eg 2016-03-02 18:27:17
, comparison with CURDATE()
failed.
See:
mysql> SELECT * FROM cad_clientes;
+----+---------------------+
| id | DataCadastro |
+----+---------------------+
| 1 | 2016-03-02 18:27:17 |
| 2 | 2016-03-02 00:00:00 |
+----+---------------------+
using CURDATE()
:
mysql> SELECT * FROM cad_clientes WHERE DataCadastro = CURDATE();
+----+---------------------+
| id | DataCadastro |
+----+---------------------+
| 2 | 2016-03-02 00:00:00 |
+----+---------------------+
Note:
I find it strange that your query does not even execute. Rebuild your table:
mysql> DESC cad_clientes;
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| DataCadastro | datetime | YES | | NULL | |
+--------------+---------------------+------+-----+---------+----------------+
Your DATETIME field is called DataCadastro , and while in your query you use DataDate . Running your query:
mysql> SELECT *,DATE_FORMAT(DataCadastro,'%Y-%m-%d') AS DataCadastros FROM cad_clientes WHERE DataCadastros = CURDATE();
ERROR 1054 (42S22): Unknown column 'DataCadastros' in 'where clause'