SQL 将时间 23:59:59.999 添加到结束日期之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7691742/
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
ADD time 23:59:59.999 to end date for between
提问by mameesh
I have been having an issue with using the following:
我在使用以下内容时遇到问题：
Column_Name BETWEEN @StartDate AND @EndDate.
This is because the @EndDate = 00:00:00.000 for the time, which doesn't pick up all the values for that day.
这是因为 @EndDate = 00:00:00.000 的时间，它不会获取当天的所有值。
How would I convert the @EndDate (Always 00:00:00.000) to always be Date + 23:59:59.999?
我如何将@EndDate（始终为 00:00:00.000）转换为始终为 Date + 23:59:59.999？
回答by dave
One option that avoids needing to add EndDate + 23:59:59.999 is to not use the betweencomparison and instead use column_name >= @StartDate and column_name < @EndDate +1
避免需要添加 EndDate + 23:59:59.999 的一种选择是不使用between比较而是使用column_name >= @StartDate and column_name < @EndDate +1
回答by Tom Hunter
Please note the accuracy and rounding of the DATETIMEtype in SQL Server 2005:
请注意SQL Server 2005 中DATETIME类型的准确性和四舍五入：
datetime values are rounded to increments of .000, .003, or .007 seconds
日期时间值四舍五入为 .000、.003 或 0.007 秒的增量
SQL Server 2008 introduced the DATETIME2type which has an accuracy of 100 nanoseconds. So in SQL Server 2008 you could do:
SQL Server 2008 引入了DATETIME2类型，其精度为 100 纳秒。所以在 SQL Server 2008 中你可以这样做：
DECLARE @d DATETIME = '2011-10-07 00:00:00.000'
SELECT DATEADD(MS, -1, DATEADD(D, 1, CONVERT(DATETIME2, @d)))
Alternatively you may want to avoid the BETWEEN operator in this case:
或者，在这种情况下，您可能希望避免 BETWEEN 运算符：
@StartDate <= Column_Name AND Column_Name < DATEADD(D, 1, @EndDate)
回答by PJNM
Since the advent of datetime2datatype, I have been struggling with this problem. To calculate the end of day as a datetime2 datatype I add the number of seconds in a day to the =date= then subtract 100 nanoseconds. Voila:
自从datetime2数据类型出现以来，我一直在努力解决这个问题。要将一天结束计算为 datetime2 数据类型，我将一天中的秒数添加到 =date= 然后减去 100 纳秒。瞧：
declare @bod datetime2
declare @eod datetime2
set @bod = cast (GETDATE() as DATE)
set @eod = DATEADD(ns, -100, DATEADD(s, 86400, @bod))
print @bod
print @eod
-- answer:
2013-12-01 00:00:00.0000000
2013-12-01 23:59:59.9999999
Now I'm off to datetimeoffsetdata type.
现在我要开始研究datetimeoffset数据类型了。
回答by Tim
You could also do this:
你也可以这样做：
select @endDate = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@endDate), 0)))
when @endDate is '5/3/2013'
当@endDate 是 '5/3/2013'
回答by Christian Specht
You can change the time in a date like this (I'm using getdate()as an example):
您可以像这样更改日期中的时间（以我getdate()为例）：
select cast(convert(char(8), getdate(), 112) + ' 23:59:59.99' as datetime)
Explanation:
解释：
convert(char(8), getdate(), 112)converts the date to yyyymmddformat (as string).
convert(char(8), getdate(), 112)将日期转换为yyyymmdd格式（作为字符串）。
Then you can just append the desired time, and convert the whole string to datetimeagain.
然后您可以附加所需的时间，然后将整个字符串datetime再次转换为。
EDIT:
编辑：
It slows the performance when you do the casting on a database column, yes.
But he has a datetime variableand he just uses the casting to change the time in the variable once
--> I see no performance issue if he uses my code to change his @EndDatevariable.
当您对数据库列进行转换时，它会降低性能，是的。
但是他有一个 datetime变量，他只是使用强制转换来更改变量中的时间一次
--> 如果他使用我的代码来更改他的@EndDate变量，我看不到性能问题。
Valid point, however. Casting is not a good solution in all situations.
然而，有效点。铸造并不是在所有情况下都是一个好的解决方案。
回答by Edward Mulraney
You can use betweenif your end date is set to 00:00:00of the next day:
between如果您的结束日期设置为00:00:00第二天，您可以使用：
ColumnName between @StartDate and convert(datetime, convert(date, @EndDate + 1))
ColumnName between @StartDate and convert(datetime, convert(date, @EndDate + 1))
This converts the next day to a date, which removes the hours information, then you convert it back to a datetimewhich adds default hour information: 00:00:00.
这会将第二天转换为 a date，删除小时信息，然后将其转换回datetime添加默认小时信息的 a ：00:00:00。
回答by Adel Mourad
--Execution / post date is 1 Feb. 2020
-- sql server this month start and end
select DATEADD(month, DATEDIFF(month, 0, getdate()), 0) -- 2020-02-01 00:00:00.000
select DATEADD(second,-1, datediff(day,0,EOMONTH(getdate()))+1) -- 2020-02-29 23:59:59.000
-- sql server this day start and end
select DATEADD(day, DATEDIFF(day, 0, getdate()), 0) -- 2020-02-01 00:00:00.000
select DATEADD(second,-1, datediff(dd,0,getdate())+1) -- 2020-02-01 23:59:59.000
-- sql server last 30 days start and end
select DATEADD(day, -30, DATEDIFF(day, 0, getdate())) -- 2020-01-02 00:00:00.000
select DATEADD(second,-1, datediff(dd,0,getdate())+1) -- 2020-02-01 23:59:59.000
回答by D_Tonthat
I first convert the original datetime to begin of the day, then add hours and seconds to it:
我首先将原始日期时间转换为一天的开始，然后添加小时和秒：
DECLARE @start DATETIME, @end DATETIME
声明@start DATETIME，@end DATETIME
SET @start = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SET @start = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SET @end = DATEADD(HOUR, 23, DATEADD(n, 59, @start))
SET @end = DATEADD(HOUR, 23, DATEADD(n, 59, @start))
PRINT @start
打印@开始
PRINT @end
打印@end
Oct 27 2017 12:00AM
2017 年 10 月 27 日上午 12:00
Oct 27 2017 11:59PM
2017 年 10 月 27 日晚上 11:59
