如何选择特定日期的行，忽略 SQL Server 中的时间 [英] How to select rows for a specific date, ignoring time in SQL Server

问题描述

给定一个带有日期时间列的表，如何查询日期与我指定的值匹配但忽略时间部分的行?

Given a table with a datetime column, how do I query for rows where the date matches the value I specify but ignores the time portion?

例如，select * from sales where salesDate = '11/11/2010'

对于这个查询，我们不关心时间.其他查询需要时间组件，所以我们不能只存储日期组件.

For this query we don't care about the time. Other queries require the time component so we can't store only the date component.

谢谢！

推荐答案

可以去掉比较时的时间分量:

You can remove the time component when comparing:

SELECT * 
FROM sales 
WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, salesDate))) = '11/11/2010'

另一种方法是将选择更改为涵盖日期开始和结束之间的所有时间:

Another approach is to change the select to cover all the time between the start and end of the date:

SELECT * 
FROM sales 
-- WHERE salesDate BETWEEN '11/11/2010 00:00:00.00' AND '11/11/2010 23:59:59.999'
WHERE salesDate BETWEEN '2020-05-18T00:00:00.00' AND '2020-05-18T23:59:59.999'

这篇关于如何选择特定日期的行，忽略 SQL Server 中的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！