How can I add Hour and minute in a single query

Question

How can I add hours and minutes in a single query?

I tried this

Select DATEADD(hh,23.59,CAST('2016-07-08' AS DATETIME))

I need to ad 23 hours and 59 minutes, but my code only adds the hours. Why?

Answer 1

How about this?

Select DATEADD(minute, 23*60 + 59, CAST('2016-07-08' AS DATETIME))

EDIT:

If you are getting a float/decimal value such as 23.59, then you can do:

Select DATEADD(minute, FLOOR(@hhmm) * 60 + (@hhmm - FLOOR(@hhmm)) * 100, CAST('2016-07-08' AS DATETIME))

Note: You can also use:

Select DATEADD(minute, FLOOR(@hhmm) * 60 + (@hhmm % 1) * 100, CAST('2016-07-08' AS DATETIME))

But I find this usage of the modulo operator slightly off-kilter.

Answer 2

Use the below code for adding time part to a date.

SELECT DATEADD(Day, DATEDIFF(Day, 0, CAST('2016-07-08' AS DATETIME)), '23:59:00.000')