我如何获得特定日期的最后可能时间

I'm trying to achieve the last possible time of a particular day eg for Date of 2008-01-23 00:00:00.000 i would need 2008-01-23 23:59:59.999 perhaps by using the dateadd function on the Date field?

我想要达到特定日期的最后可能时间，例如2008-01-23 00：00：00.000的日期我需要2008-01-23 23：59：59.999或许可以通过使用日期上的dateadd函数领域？

4 个解决方案

#1

The answer is SELECT DATEADD(ms, -3, '2008-01-24'), the explanation is below.

答案是SELECT DATEADD（ms，-3，'2008-01-24'），解释如下。

From Marc's blog:

来自Marc的博客：

But wait, Marc... you said you like to use BETWEEN, but that query doesn't have one... that's because BETWEEN is inclusive, meaning it includes the end-points. If I had an Order that was due at midnight of the first day of the next month it would be included. So how do you get the appropriate value for an end-of-period? It's most certainly NOT by using date-parts to assemble one (but is you must, please remember that it's 23:59:59.997 as a maximum time... don't forget the milliseconds). To do it right, we use the incestuous knowledge that Microsoft SQL Server DATETIME columns have at most a 3 millisecond resolution (something that is not going to change). So all we do is subtract 3 milliseconds from any of those end-of-period formulas given above. For example, the last possible instant of yesterday (local time) is:

但是等等，Marc ......你说你喜欢使用BETWEEN，但是那个查询没有...那是因为BETWEEN是包容性的，这意味着它包含了终点。如果我订的是下个月第一天午夜到期的订单，那么它将包括在内。那么如何在期末获得适当的价值呢？这肯定不是通过使用日期部分来组装一个（但是你必须记住它是23：59：59.997作为最大时间......不要忘记毫秒）。为了做到这一点，我们使用Microsoft SQL Server DATETIME列最多具有3毫秒分辨率（不会改变的东西）的乱语。所以我们所做的就是从上面给出的任何一个句末期公式中减去3毫秒。例如，昨天（当地时间）的最后一个可能时刻是：

    SELECT DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))

So to do the orders due this month as a BETWEEN query, you can use this:

因此，要将本月到期的订单作为BETWEEN查询，您可以使用：

    SELECT [ID]
    FROM [dbo].[Orders]
    WHERE [ShipDue] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()), 0)
    AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()) + 1, 0))

Remember, always make sure that you do math against input parameters, NOT columns, or you will kill the SARG-ability of the query, which means indexes that might have been used aren't.

请记住，始终确保您对输入参数，非列进行数学运算，否则您将终止查询的SARG能力，这意味着可能已使用的索引不是。

#2

SELECT DATEADD(ms, -2, DATEADD(dd, 1, DATEDIFF(dd, 0, GetDate())))

I thought you had c# at first.. I will leave this here in case anyone else stumbles across this.

我以为你最初有c＃..我会留在这里以防其他人偶然发现这个。

DateTime now = DateTime.Now;
DateTime endofDay = now.Date.AddDays(1).AddMilliseconds(-1);

You can replace the 'now' variable with whatever day you are trying to figure out

您可以将“现在”变量替换为您想要弄清楚的任何一天

#3

Add -1 milliseconds to the start of the next day (DateAdd even supports nanoseconds, if you want to get real fine).

添加-1毫秒到第二天的开始（DateAdd甚至支持纳秒，如果你想要真正的好）。

But most likely you just want to use this value in a comparison, and in that case it's even simpler.

但很可能你只想在比较中使用这个值，在这种情况下它甚至更简单。

Rather than something like this:

而不是像这样的东西：

AND @CompareDate <= [LastTimeforThatday]

or this:

或这个：

@compareDate BETWEEN [StartDate] AND [LastTimeforThatday]

Do it like this:

像这样做：

AND @CompareDate < [BeginningOfNextDay]

or this:

或这个：

AND (@CompareDate >= [StartDate] AND @CompareDate < [BeginningOfNextDay])

#4

Why back into it?

为什么回到它？

SELECT DATEADD(ms, 86399997, *yourDate*)