我有一个表,它以这种格式存储日期:
2016年07月29日00:00:00
我需要的是向用户显示来自一天的所有数据,例如
+-------+-------------+---------------------+
| id | product | date |
+-------+-------------+---------------------+
| 1 | one |7/27/2016 9:29:14 AM |
| 2 | two |7/28/2016 7:53:42 AM |
| 3 | three |7/28/2016 9:53:24 PM |
+-------+-------------+---------------------+因此,我想返回:在2016年7月28日,记录了2个新产品。我怎样才能做到这一点呢?
以下是数据类型的字段:
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product | varchar(255) | NO | MUL | NULL | |
| date | varchar(255) | NO | MUL | NULL | |
+------------------+------------------+------+-----+---------+----------------+数据以DateTime格式存储在c#中。我尝试过在两者之间:
SELECT * FROM products where date between '7/29/2016 00:00:00 AM' and '7/29/2016 11:59:00 PM';不起作用。但是,当我这样说的时候:
SELECT * FROM products where date between '7/01/2016 00:00:00 AM' and '7/31/2016 11:59:00 PM';它返回当月的产品。
发布于 2016-07-29 21:09:05
下面的MySQL查询应该适用于您:
SELECT * FROM products
WHERE date >= '2016-07-28 00:00:00' AND date < '2016-07-29 00:00:00'或者使用BETWEEN
SELECT * FROM products
WHERE date BETWEEN '2016-07-28 00:00:00' AND '2016-07-28 23:59:59'如果您的日期字段当前存储为字符串,只需先使用STR_TO_DATE进行转换
SELECT * FROM products
WHERE STR_TO_DATE(date, '%c/%e/%Y %h:%i:%s %p') BETWEEN '2016-07-28 00:00:00' AND '2016-07-28 23:59:59'或者,如果您只想要一天(并且在日期字段上有一个索引),只需使用LIKE语法来利用这些索引:
SELECT * FROM products
WHERE date LIKE '7/28/2016 %'发布于 2016-07-29 21:13:43
SELECT * FROM Table_Name
WHERE date BETWEEN 'startdatetime' AND 'enddatetime'此查询可能适用于您。
https://stackoverflow.com/questions/38659734
复制相似问题