Current date with ending time in sql

Question

I have the query like this:

select cast(convert(varchar(10), getdate(), 110) as datetime)

This is returning out put like this:

currentdate
-----------------------
2013-07-28 00:00:00.000

but I want to get current date with ending time. My expected output is like this:

currentdate
-----------------------
2013-07-28 23:59:59

Answer 1

Well, in that case, you need to give your varchar that you're converting to more characters!

select cast(convert(varchar(30), getdate(), 110) as datetime)
                            **

And the question really is: why are you converting the output from GETDATE() into a Varchar and then back to a DATETIME in the first place??

Can't you just use

SELECT GETDATE()

and that's all?

Update: OK, so you want to get the current day, but the fixed time of 23:59:59?

Try this:

SELECT
   CAST(CONVERT(VARCHAR(10), GETDATE(), 110) + ' 23:59:59' AS DATETIME)

Answer 2

Either of the below will do it for display purposes;

SELECT DATEADD(s, -1, DATEADD(day, 1, 
          CONVERT(DATETIME, CONVERT(DATE, GETDATE()))));

...or...

SELECT DATEADD(s, -1, DATEADD(day, 1,
          DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)));

You could also just set a fixed time of 23:59:59, but you'd end up with an invalid time at leap second adjustments (which may or may not be ok)

If you want to use it for comparison purposes, it's better to just use "less than the next date".