CODEWITHSUNDEEP
sql-servert-sql
user2603688
user2603688

Reputation: 171

Current date with ending time in sql

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

Upvotes: 3

Views: 11869

Answers (4)

Arghya C
Arghya C

Reputation: 10068

Just for display (without milliseconds)

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

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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

Upvotes: 1

Louie Bao
Louie Bao

Reputation: 1732

If you want to return the current date with the end time, then you will have to append the end time manually:

select cast(convert(varchar(10), getdate(), 110) + ' 23:59:59.997' as datetime)

Upvotes: 0

marc_s
marc_s

Reputation: 754478

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)

Upvotes: 4

Related Questions