将 SQL 日期时间舍入到午夜
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8555316/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rounding SQL DateTime to midnight
提问by henryaaron
I am having a small problem with my SQL query. I'm using the GETDATE function, however, let's say I execute the script at 5PM, it will pull up records between 12/12/2011 5PM to 12/18/2011 5PM. How can I make it pull up records for the whole entire 12/12/2011 - 12/18/2011 basically ignore time.
我的 SQL 查询有一个小问题。我正在使用 GETDATE 函数，但是，假设我在下午 5 点执行脚本，它将在 12/12/2011 5PM 到 12/18/2011 5PM 之间提取记录。我怎样才能让它提取整个 12/12/2011 - 12/18/2011 的记录基本上忽略时间。
My script:
我的脚本：
WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate > (GETDATE()-6)
回答by DaveShaw
In SQL Server 2008 and newer you can cast the DateTimeto a Date, which removes the time element.
在 SQL Server 2008 和更新版本中，您可以将 强制转换DateTime为 a Date，这将删除时间元素。
WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate >= (cast(GETDATE()-6 as date))
In SQL Server 2005 and below you can use:
在 SQL Server 2005 及以下版本中，您可以使用：
WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate >= DateAdd(Day, Datediff(Day,0, GetDate() -6), 0)
回答by Darrel Lee
Here is the simplest thing I've found
这是我发现的最简单的事情
-- Midnight floor of current date
SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))
The DATEDIFF returns the integer number of days before or since 1900-1-1, and the Convert Datetime obligingly brings it back to that date at midnight.
DATEDIFF 返回 1900 年 1 月 1 日之前或之后的整数天数，而转换日期时间会在午夜将其恢复到该日期。
Since DateDiff returns an integer you can use add or subtract days to get the right offset.
由于 DateDiff 返回一个整数，您可以使用加减天数来获得正确的偏移量。
SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()) + @dayOffset)
This isn't rounding this is truncating...But I think that is what is being asked. (To round add one and truncate...and that's not rounding either, that the ceiling, but again most likely what you want. To really round add .5 (does that work?) and truncate.
这不是四舍五入，而是截断......但我认为这就是所要求的。（四舍五入加一并截断......这也不是四舍五入，那是天花板，但又很可能是你想要的。真正四舍五入加上 0.5（这行得通吗？）并截断。
It turns out you can add .5 to GetDate() and it works as expected.
事实证明，您可以将 .5 添加到 GetDate() 并按预期工作。
-- Round Current time to midnight today or midnight tomorrow
SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE() + .5))
I did all my trials on SQL Server 2008, but I think these functions apply to 2005 as well.
我在 SQL Server 2008 上进行了所有试验，但我认为这些功能也适用于 2005。
回答by edvox1138
--
-- SQL DATEDIFF getting midnight time parts
--
SELECT GETDATE() AS Now,
Convert(DateTime, DATEDIFF(DAY, 0, GETDATE())) AS MidnightToday,
Convert(DateTime, DATEDIFF(DAY, -1, GETDATE())) AS MidnightNextDay,
Convert(DateTime, DATEDIFF(DAY, 1, GETDATE())) AS MidnightYesterDay
go
Now MidnightToday MidnightNextDay MidnightYesterDay
-------------------- --------------------- --------------------- ---------------------
8/27/2014 4:30:22 PM 8/27/2014 12:00:00 AM 8/28/2014 12:00:00 AM 8/26/2014 12:00:00 AM
回答by Jeremy Atkinson
SELECT getdate()
Result: 2012-12-14 16:03:33.360
结果：2012-12-14 16:03:33.360
SELECT convert(datetime,convert(bigint, getdate()))
Result 2012-12-15 00:00:00.000
结果 2012-12-15 00:00:00.000
回答by UttamG
Try using this.
尝试使用这个。
WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate >= CONVERT(DATE, GETDATE())
回答by MatBailie
As @BassamMehanni mentioned, you can cast as DATE in SQL Server 2008 onwards...
正如@BassamMehanni 提到的，您可以在 SQL Server 2008 中将日期转换为日期...
SELECT
*
FROM
yourTable
WHERE
dateField >= CAST(GetDate() - 6 AS DATE)
AND dateField < CAST(GetDate() + 1 AS DATE)
The second condition can actually be just GetDate(), but I'm showing this format as an example of Less Than DateXto avoid having to cast the dateField to a DATE as well, thus massively improving performance.
第二个条件实际上可以只是GetDate()，但我将这种格式显示为一个示例，Less Than DateX以避免将 dateField 也强制转换为 DATE，从而大大提高性能。
If you're on 2005 or under, you can use this...
如果您使用的是 2005 或更低版本，则可以使用此...
SELECT
*
FROM
yourTable
WHERE
dateField >= DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) - 6, 0)
AND dateField < DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) + 1, 0)
回答by Jean-Louis Gervais
This might look cheap but it's working for me
这可能看起来很便宜，但它对我有用
SELECT CONVERT(DATETIME,LEFT(CONVERT(VARCHAR,@dateFieldOrVariable,101),10)+' 00:00:00.000')
SELECT CONVERT(DATETIME,LEFT(CONVERT(VARCHAR,@dateFieldOrVariable,101),10)+' 00:00:00.000')
回答by ChrisM
You could round down the time.
你可以缩短时间。
Using ROUNDbelow will round it down to midnight.
使用ROUND下面会将其舍入到午夜。
WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate > CONVERT(datetime, (ROUND(convert(float, getdate()-6.5),0)))
回答by Ben
You can convert the datetime to a date then back to a datetime. This will reset the timestamp.
您可以将日期时间转换为日期，然后再转换回日期时间。这将重置时间戳。
select getdate() --2020-05-05 13:53:35.863 select cast(cast(GETDATE() as date) as datetime) --2020-05-05 00:00:00.000
回答by Bassam Mehanni
I usually do
我通常做
SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR, MyTable.dateField, 101) = CONVERT(VARCHAR, GETDATE(), 101)
if you are using SQL SERVER 2008, you can do
如果您使用的是 SQL SERVER 2008，则可以
SELECT *
FROM MyTable
WHERE CAST(MyTable.dateField AS DATE) = CAST(GETDATE() AS DATE)
Hope this helps
希望这可以帮助
