How to query the data of a certain day in mysql

Method: 1. Use the "date_format" function, the syntax is "where date_format(date,'%Y-%m-%d')='year-month-day'"; 2. Use the datediff function, Syntax "WHERE(datediff(time,'year-month-day')=0)".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to query the data of a certain day in mysql

1. DATE_FORMAT function

WHERE(datediff(time,'year-month-day')= The DATE_FORMAT() function is used to display date/time data in different formats.

Syntax

DATE_FORMAT(date,format)

The date parameter is a legal date. format specifies the output format of the date/time.

Examples are as follows:

select * from [table_name] where date_format([date_name],'%Y-%m-%d') = '2022-05-13'

2. DATEDIFF function

DATEDIFF() function returns the number of days between two dates.

Syntax

DATEDIFF(date1,date2)

The date1 and date2 parameters are legal date or date/time expressions.

Examples are as follows:

SELECT * FROM [table_name] WHERE ( datediff ( update_time , '2022-05-13' ) = 0 )

Extended knowledge:

SELECT * FROM [table_name] WHERE substring(Convert(char(10),update_time ,112),1,8)='20220513' 
SELECT * FROM [table_name] WHERE update_time between '2022-05-13 00:00:00' and '2022-05-13 23:59:59' 
SELECT * FROM [table_name] WHERE year(update_time ) = 2022 and month(update_time )= 05 and day(update_time ) = 13
SELECT * FROM [table_name] WHERE update_time > &#39;2017-09-27&#39; and update_time < &#39;2022-05-13&#39;

Recommended learning: mysql video tutorial

The above is the detailed content of How to query the data of a certain day in mysql. For more information, please follow other related articles on the PHP Chinese website!