Set time part of datetime variable to 18:00

Question

Hi. I need to set datetime variable to two days from now but it's time part must be 18:00.

For example if i call getdate() now i'll get 2010-05-17 13:18:07.260. I need to set it to 2010-05-19 18:00:00.000.

Does anybody have a good snippet for that or any ideas how to do it right?

Answer 1

SELECT DATEADD(hh, 24 * 2 + 18, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

This truncates the current date and adds 2 days and 18 hours to it (24 * 2 + 18).

A possible variation:

SELECT DATEADD(hh, 18, DATEADD(dd, DATEDIFF(dd, -2, GETDATE()), 0))

Answer 2

Select DateAdd(hour, 18, DateAdd(day, 2, cast(floor(cast(getdate() as float))as datetime)))