如何仅从 MySQL 的 DATETIME 字段中选择日期？

How to select only date from a DATETIME field in MySQL?

提问by DiegoP.

I have a table in the MySQL database that is set up with DATETIME. I need to SELECTin this table only by DATE and excluding the time.

我在 MySQL 数据库中有一个表，该表设置为DATETIME. 我只需要SELECT在此表中按 DATE 并排除时间。

How do I SELECTin this table by only date and bypassing the time, even if that specific column is set to DATETIME?

SELECT即使该特定列设置为 ，我如何仅按日期并绕过时间在此表中DATETIME？

Example

例子

Now it is: 2012-01-23 09:24:41

现在它是： 2012-01-23 09:24:41

I need to do a SELECTonly for this: 2012-01-23

我只需要为此做一个SELECT：2012-01-23

回答by Balaswamy Vaddeman

SELECT DATE(ColumnName) FROM tablename;

SELECT DATE(ColumnName) FROM tablename;

More on MySQL DATE() function.

有关MySQL DATE() 函数的更多信息。

回答by sushant goel

you can use date_format

你可以使用 date_format

select DATE_FORMAT(date,'%y-%m-%d') from tablename

for time zone

对于时区

sql2 = "SELECT DATE_FORMAT(CONVERT_TZ(CURDATE(),'US/Central','Asia/Karachi'),'%Y-%m-%d');"

回答by user1061865

You can use select DATE(time) from appointment_detailsfor date only

您只能select DATE(time) from appointment_details用于日期

or

或者

You can use select TIME(time) from appointment_detailsfor time only

您只能select TIME(time) from appointment_details用于时间

回答by Mohammed Aly

Try to use
for today:

今天尝试使用：

SELECT * FROM `tbl_name` where DATE(column_name) = CURDATE()

for selected date:

对于所选日期：

SELECT * FROM `tbl_name` where DATE(column_name) = DATE('2016-01-14')

回答by Santosh D.

In MYSQL we have function called DATE_FORMAT(date,format). In your case your select statement will become like this:-

在 MYSQL 中，我们有一个名为DATE_FORMAT(date,format) 的函数。在您的情况下，您的选择语句将变成这样：-

SELECT DATE_FORMAT(dateTimeFieldName,"%a%m%Y") as dateFieldName FROM table_name

SELECT DATE_FORMAT(dateTimeFieldName,"%a%m%Y") as dateFieldName FROM table_name

For more information about Mysql DATE and TIME functions click here.

有关 Mysql DATE 和 TIME 函数的更多信息，请单击此处。

回答by Mike

I tried doing a SELECT DATE(ColumnName), however this does not workfor TIMESTAMPcolumns^?because they are stored in UTC and the UTC date is used instead of converting to the local date. I needed to select rows that were on a specific date in my time zone, so combining my answer to this other questionwith Balaswamy Vaddeman's answer to this question, this is what I did:

我试过做一个SELECT DATE(ColumnName)，但是这对列不起作用^？因为它们以 UTC 存储，并且使用 UTC 日期而不是转换为本地日期。我需要选择时区中特定日期的行，因此将我对另一个问题的回答与Balaswamy Vaddeman 对这个问题的回答结合起来，这就是我所做的：TIMESTAMP

If you are storing dates as DATETIME

如果您将日期存储为 DATETIME

Just do SELECT DATE(ColumnName)

做就是了 SELECT DATE(ColumnName)

If you are storing dates as TIMESTAMP

如果您将日期存储为 TIMESTAMP

Load the time zone data into MySQLif you haven't done so already. For Windows servers see the previous link. For Linux, FreeBSD, Solaris, and OS X servers you would do:

如果您还没有这样做，请将时区数据加载到 MySQL 中。对于 Windows 服务器，请参阅上一个链接。对于 Linux、FreeBSD、Solaris 和 OS X 服务器，您可以执行以下操作：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Then format your query like this:

然后像这样格式化您的查询：

SELECT DATE(CONVERT_TZ(`ColumnName`, 'UTC', 'America/New_York'))

You can also put this in the WHEREpart of the query like this (but note that indexes on that column will not work):

您也可以WHERE像这样将其放在查询的一部分中（但请注意，该列上的索引将不起作用）：

SELECT * FROM tableName
WHERE DATE(CONVERT_TZ(`ColumnName`, 'UTC', 'America/New_York')) >= '2015-02-04'

(Obviously substitute America/New_Yorkfor your local time zone.)

（显然代替America/New_York您当地的时区。）

---

---

?The only exception to this is if your local time zone is GMT and you don't do daylight savings because your local time is the same as UTC.

? 唯一的例外是如果您的本地时区是 GMT，并且您不执行夏令时，因为您的本地时间与 UTC 相同。

回答by krish kim

Simply You can do

简单地你可以做到

SELECT DATE(date_field) AS date_field FROM table_name

回答by Richie

Try SELECT * FROM Profiles WHERE date(DateReg)=$datewhere $date is in yyyy-mm-dd

尝试 SELECT * FROM Profiles WHERE date(DateReg)=$date$date 在 yyyy-mm-dd 中的位置

Alternatively SELECT * FROM Profiles WHERE left(DateReg,10)=$date

或者 SELECT * FROM Profiles WHERE left(DateReg,10)=$date

Cheers

干杯

回答by aymsoft

Yo can try this:

你可以试试这个：

SELECT CURDATE();

If you check the following:

如果您检查以下内容：

SELECT NOW(); SELECT DATE(NOW()); SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

You can see that it takes a long time.

你可以看到这需要很长时间。

回答by Sai Charan Tsalla

Please try this answer.

请试试这个答案。

SELECT * FROM `Yourtable` WHERE date(`dateField`) = '2018-09-25'