MySQL中日期格式转换（含时分秒转当日零点）及同日期数据过滤失效问题咨询

Hey there, let's tackle your two MySQL date-related questions one by one:

You’ve got a few simple, efficient ways to truncate a datetime to the start of its date:

• Using the DATE() function: This extracts just the date portion, which MySQL automatically treats as YYYY-MM-DD 00:00:00 when cast to datetime. Example:

  SELECT CAST(DATE('2021-03-18 14:34:34') AS DATETIME) AS truncated_datetime;
  

  Or if you want an explicitly formatted string:

  SELECT DATE_FORMAT('2021-03-18 14:34:34', '%Y-%m-%d 00:00:00') AS truncated_datetime;
  

• Range-based truncation (useful if you want to avoid function calls for indexing):

  SELECT TIMESTAMPADD(HOUR, -HOUR('2021-03-18 14:34:34'), '2021-03-18 14:34:34') AS truncated_datetime;
  

The root of your problem is the BETWEEN clause you used—you’re asking for records where MODIFIED_DATE is exactly 2021-03-18 00:00:00, but your table stores timestamps with non-zero hours/minutes/seconds, so no matches are returned.

Here are two reliable ways to fetch all records from March 18, 2021:

• Date matching (simple, watch for index usage):
  Use the DATE() function to compare only the date portion:

  SELECT MODIFIED_DATE FROM instance_history WHERE DATE(MODIFIED_DATE) = '2021-03-18';
  

  Note: If MODIFIED_DATE has an index, wrapping it in DATE() will prevent MySQL from using that index. For large tables, use the method below instead.
• Range query (index-friendly):
  Define the start of your target date and the start of the next day—this includes all timestamps from March 18, 00:00:00 up to (but not including) March 19, 00:00:00:

  SELECT MODIFIED_DATE FROM instance_history 
  WHERE MODIFIED_DATE >= '2021-03-18 00:00:00' 
    AND MODIFIED_DATE < '2021-03-19 00:00:00';
  

The cast(cast(left(...) as date) as datetime) approach you tried would work for truncating dates, but it didn’t fix your query because you still used a BETWEEN clause that only matched the exact midnight timestamp. Swap that condition with one of the above, and you’ll get the records you need.

内容的提问来源于stack exchange，提问作者Swapnil Chavan