如何将SQL中的datetime字段转换为date类型？

你的查询漏掉部分数据的原因是：rf.submissiondate是datetime类型，当你用between '2020-01-01' and '2022-12-31'时，数据库会自动把'2022-12-31'解析成2022-12-31 00:00:00，所以当天任何晚于这个时间的记录都会被过滤掉。

下面提供两种可行的解决办法：

方法1：将Datetime转换为日期类型 #

如果需要在结果中只显示日期格式，或者必须基于日期做筛选，可以根据你使用的数据库选择对应的转换函数：

MySQL/MariaDB #

使用DATE()函数提取日期部分：

SELECT ed.procedurecode, ed.encounterid, DATE(rf.submissiondate) AS submission_date
FROM encounterdetail AS ed
JOIN receivedfile AS rf ON ed.submissiondatekey = rf.submissiondatekey
WHERE DATE(rf.submissiondate) BETWEEN '2020-01-01' AND '2022-12-31'
  AND rf.statustypeid = '1';

SQL Server #

使用CAST()或CONVERT()函数转换为DATE类型：

SELECT ed.procedurecode, ed.encounterid, CAST(rf.submissiondate AS DATE) AS submission_date
FROM encounterdetail AS ed
JOIN receivedfile AS rf ON ed.submissiondatekey = rf.submissiondatekey
WHERE CAST(rf.submissiondate AS DATE) BETWEEN '2020-01-01' AND '2022-12-31'
  AND rf.statustypeid = '1';

PostgreSQL #

用类型转换语法或DATE()函数：

SELECT ed.procedurecode, ed.encounterid, rf.submissiondate::DATE AS submission_date
FROM encounterdetail AS ed
JOIN receivedfile AS rf ON ed.submissiondatekey = rf.submissiondatekey
WHERE rf.submissiondate::DATE BETWEEN '2020-01-01' AND '2022-12-31'
  AND rf.statustypeid = '1';

方法2：调整查询条件（推荐，性能更优） #

这种写法不需要转换字段，直接把结束条件设为目标日期的下一天，既能包含2022-12-31当天所有时间的记录，又能利用rf.submissiondate上的索引，避免函数转换导致的性能损耗：

SELECT ed.procedurecode, ed.encounterid, rf.submissiondate
FROM encounterdetail AS ed
JOIN receivedfile AS rf ON ed.submissiondatekey = rf.submissiondatekey
WHERE rf.submissiondate >= '2020-01-01'
  AND rf.submissiondate < '2023-01-01'
  AND rf.statustypeid = '1';

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