Get current date in mysql

4

Colleagues.

I have a table that stores the dates of the record with DATETIME (), but I need to check if the date of the record is equal to the current date, so I did it as follows, but it does not work:

SELECT *,DATE_FORMAT(DataCadastro,'%Y-%m-%d') AS DataCadastros FROM cad_clientes WHERE DataCadastros = CURDATE();
    
asked by anonymous 01.02.2016 / 22:51

2 answers

4

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'
    
02.03.2016 / 22:49
1

The type datetime has the format Y-m-d H:i:s , that is, the date and time.

To compare the date by ignoring the time, use the function DATE()

Example:

SELECT * FROM cad_clientes WHERE DATE(DataCadastro) = CURDATE();
    
03.03.2016 / 15:29