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

Question

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

Answer 1

Try to execute one more statement:

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

Answer 2

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.