MySQL如何将datetime字段的时间部分重置为00:00:00且保留datetime类型

核心思路是对PurchaseDate按天截断，保留datetime类型返回，避免仅用date()函数返回纯日期类型。

MySQL #

两种写法均返回datetime类型：

• 显式转换date结果为datetime

SELECT 
  PurchaseDate,
  CAST(DATE(PurchaseDate) AS DATETIME) AS StartOfDay
FROM tmp;

• 时间偏移计算（无需额外类型转换）

SELECT 
  PurchaseDate,
  PurchaseDate - INTERVAL TIME_TO_SEC(PurchaseDate) SECOND AS StartOfDay
FROM tmp;

SQL Server #

• 全版本兼容写法（你之前尝试的DATEDIFF搭配DATEADD使用即可实现需求）

SELECT 
  PurchaseDate,
  DATEADD(DAY, DATEDIFF(DAY, 0, PurchaseDate), 0) AS StartOfDay
FROM tmp;

• 2022及以上版本简化写法

SELECT 
  PurchaseDate,
  DATETRUNC(DAY, PurchaseDate) AS StartOfDay
FROM tmp;

PostgreSQL #

直接用内置截断函数返回timestamp类型：

SELECT 
  PurchaseDate,
  DATE_TRUNC('day', PurchaseDate) AS StartOfDay
FROM tmp;

Oracle #

TRUNC函数默认截断到天，返回的DATE类型自带00:00:00时间部分：

SELECT 
  PurchaseDate,
  TRUNC(PurchaseDate) AS StartOfDay
FROM tmp;

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