Set time part of datetime variable to 18:00

Question

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

I had to do something similar, create a procedure to run from a certain time the previous day to a certain time on the current day This is what I did to set the start date to 16:30 on the previous day, basically subtract the parts you don't want to get them back to 0 then add the value that you want it to be.

-- Set Start Date to previous day and set start time to 16:30.00.000

SET @StartDate = GetDate()
SET @StartDate = DateAdd(dd,- 1, @StartDate) 
SET @StartDate = DateAdd(hh,- (DatePart(hh,@StartDate))+16, @StartDate) 
SET @StartDate = DateAdd(mi,- (DatePart(mi,@StartDate))+30, @StartDate) 
SET @StartDate = DateAdd(ss,- (DatePart(ss,@StartDate)), @StartDate) 
SET @StartDate = DateAdd(ms,- (DatePart(ms,@StartDate)), @StartDate) 

Hope this helps someone.

Answer 3

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

Answer 4

In short, the answer is...

DECLARE @MyDate DATETIME = DATEADD(HOUR, 18, CONVERT(DATETIME, CONVERT(DATE, GETDATE()+2))) [6pm Today]

It probably looks pretty complicated, but believe me when I say it looks a lot worse than it really is.

To explain each function call as a sequence of steps for the query above is as follows:

1. GETDATE(): Gets the current date and time as a DATETIME value.
2. +2: Add two days, since each whole number in DATETIME calulations represents a day, with fractions representing fractions of a day.
3. CONVERT(DATE,@REF): Convert to date format (to remove the time element)
4. CONVERT(DATETIME,@REF): Convert back to DateTime (Time element defaults to midnight 00:00)
5. DATEADD(HOUR,@NUM,@REF): Add 12 + 6 = 18 hours to get 6 in the afternoon

Notes:

• Change @MyDate to reflect the name of your own variable, bearing in mind that variables have a @ prefix.

• Years may be added by wrapping the query between the = and the [6pm Today] in a DATEADD(YEAR,@NUM,@REF) function.

• Months may be added by wrapping the query between the = and the [6pm Today] in a DATEADD(MONTH,@NUM,@REF) function.
• Minutes may be added by wrapping the query between the = and the [6pm Today] in a DATEADD(MINUTE,@NUM,@REF) function.
• Seconds may be added by wrapping the query between the = and the [6pm Today] in a DATEADD(SECOND,@NUM,@REF) function.

• Milliseconds may be added by wrapping the query between the = and the [6pm Today] in a DATEADD(MILLISECOND,@NUM,@REF) function.

• I personally avoid DATEDIFF function, because it returns a 32bit int, which overflows often since the DateTime value type is stored internally as a 64bit value and represents dates and times with values ranging from 00:00:00 (midnight), January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) in the Gregorian calendar.

• I use CONVERT instead of CAST since CAST calls the CONVERT function. It probably makes barely any difference to execution time, but if it does then it makes sense to call CONVERT directly.

• The white-space in the queries is mostly optional.