Solutions:
1. Maintaining the current table structure:
Format the column Datacadastro within the clause where
to compare two values DATE
:
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 structure of the table:
Note: only use this solution if you are sure you do not need the schedules contained in the registors in the field Datacadastro. Once you change the field type in the table, 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 field Datacadastro is the type DATETIME
, that is to say, date + time, and you try to perform a comparison with the value returned by the function CURDATE()
, which returns only one date in the format 'YYYY-MM-DD'
.
Your research would-be right if your records always had the value of Datacadastro with the time reset, as in 2016-03-02 00:00:00
, but when the column value has some set time, e.g. 2016-03-02 18:27:17
, the comparison with CURDATE()
glitch.
Look at that:
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 |
+----+---------------------+
Observing:
I find it strange that your query even performs. I rebuilt 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 yet in your query you use Dataregistries. 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'
When you say it’s stored with
DATETIME()
I’m not sure if the column in the database is in formatDATETIME
orVARCHAR
. ISDATETIME
orVARCHAR
? If you don’t try to useSELECT STR_TO_DATE(DataCadastro,'%Y-%m-%d') etc...
– Sergio
has already tried to make the comparison with the now()?
– alexandre9865
The format of the field is Datetime.
– user24136
Hi Alexandre. Yes. It didn’t work either...
– user24136
has come to take a look at the documentation, to see if the return of now or Kurdish is equal to this format that you are using? ('%Y-%m-%d')
– alexandre9865