SQL Server Time format in 23:59:59 and not 00:00:00

Alex 0 Reputation points

I have the following code. I would like the second (to) date in time format '23:59:59' and not '00:00:00' . How can this be achieved please?

declare @Date datetime 
declare @StartDate datetime 
declare @EndDate DateTime

set @date = GetDate()
Set @StartDate = DATEADD(M, DATEDIFF(M,0,@date),0)
set @EndDate = DATEADD(DD,DATEDIFF(DD,0, @date),0)

select @StartDate,@EndDate
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 113.7K Reputation points

    Try to execute one more statement:

    set @EndDate = dateadd(second, -1, @EndDate)

  2. Erland Sommarskog 103.5K Reputation points MVP

    Hm, what drives you to ask this question? I suspect this is because your next step to run something like

    WHERE datetimecol BETWEEN @StartDate and @EndDate

    If that is the case, do you really want to miss events that occurred 23:59:59.500?

    The correct way to write this is:

    WHERE datetimecol >= cast(getdate() AS date)
      AND datetimecol < dateadd(DAY, 1, cast(getdate() AS date))

    Then you don't need bother about seconds.

    0 comments No comments