Get current date in mysql

Asked

Viewed 22,129 times

4

Colleagues.

I have a table that stores the registration dates with DATETIME(), but I need to check if the registration date is equal to the current date, so I did it as below, but it doesn’t work:

SELECT *,DATE_FORMAT(DataCadastro,'%Y-%m-%d') AS DataCadastros FROM cad_clientes WHERE DataCadastros = CURDATE();
mysql sql

  • When you say it’s stored with DATETIME() I’m not sure if the column in the database is in format DATETIME or VARCHAR. IS DATETIME or VARCHAR? If you don’t try to use SELECT STR_TO_DATE(DataCadastro,'%Y-%m-%d') etc...

  • has already tried to make the comparison with the now()?

  • The format of the field is Datetime.

  • Hi Alexandre. Yes. It didn’t work either...

  • 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')

2 answers

6


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'

1

The guy 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();

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.