How do I get the last possible time of a particular day

Hi ,

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?

From stackoverflow test

• Surely 23:59:59 is always the last possible time of the day?

  Commodore Jaeger : Not if we take leap seconds [http://en.wikipedia.org/wiki/Leap_second] into account, but many systems simply count 23:59:59 twice instead of adding 23:59:60
  From Valerion

• 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.

  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

  Danimal : check the tag -- he wants to do it in tsql
  From Shaun Bowe

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

  From Marc's blog:

  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:

      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:

      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.

  test : Sorry but this selected the latest time of day before the date I wanted..... rather than that particular day

  Pandincus : This is great! A really, really easy way to get the end of the day.
  From bdukes

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

  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])
  

  David B : As you say, excluding the endpoint is a very stable approach for these problems. It also allows for durations to be calculated without fudging.
  From Joel Coehoorn

• Could you please say how you intend to use this last possible time of a particular day? Perhaps we can find an elegant way around it.

  From Nuno G