Set time portion of a datetime variable

I am working on a query that will be an automated job. It needs to find all the transactions between 8 PM and 8 PM for the last day. I was thinking of doing so开发者_StackOverflow中文版mething like this

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @start_date = DATEADD(DAY, -2, GETDATE())
SET @end_date = DATEADD(DAY, -1, GETDATE())

For an automated query this works good at figuring out the date portion. But the TIME portion of the variable is the current time that the query executes. Is there a quick simple way to hard code the time portion of both variables to be 8:00 PM?

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @start_date = DATEADD(hour, 20, DATEDIFF(DAY, 2, GETDATE()))
SET @end_date = @start_date + 1

select @start_date, @end_date

This will also work:

DECLARE @start_date datetime
DECLARE @end_date datetime

SET @start_date = LEFT(CONVERT(nvarchar, DATEADD(DAY, -2, GETDATE()), 120), 11) + N'20:00:00'
SET @end_date = @start_date + 1

select @start_date, @end_date

Although cyberkiwi's answer is very clever! =)

I needed to pull a date from the database and append 3:00 Pm to it. I did it this way

select dateadd(hour, 15, datediff(day, 0, myDatabaseDate)) 
from dbo.myDatabaseTable
where myDatabaseId = 1

The result that it returned was 2017-10-01 15:00:00.000. The date in the database is 2017-10-01. The solution that I proposed was to keep my current date. I added 0 days to my existing date. I gave it 15:00 hours and it worked like a charm.

In case of just updating a particular part of the datetime you can use SMALLDATETIMEFROMPARTS like:

UPDATE MyTable 
SET MyDate = SMALLDATETIMEFROMPARTS(YEAR(MyDate), MONTH(MyDate), DAY(MyDate), <HoursValue>, <MinutesValue>) 

In other cases it may be required to copy parts of datetime to other or update only certain parts of the datetime:

UPDATE MyTable 
SET MyDate = SMALLDATETIMEFROMPARTS(YEAR(MyDate), MONTH(MyDate), DAY(MyDate), DATEPART(hour, MyDate), DATEPART(minute, MyDate)) 

Refer SQL Server Date/Time related API references for more such functions

DECLARE @start_date DATETIME = DATEADD(HOUR, 20, DATEADD(MINUTE, 00, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))) - 2
DECLARE @end_date DATETIME = DATEADD(HOUR, 20, DATEADD(MINUTE, 00, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))) - 1

Notes:

• GETDATE() + X is the equivalent of DATEADD(DAY, X, GETDATE()).
• Converting a DATEIME to a DATE and then back to a DATETIME again sets the time to midnight i.e. 00:00:00.000.
• Seperate SET and DECLARE statements are unnecessary, but just in case it helps later, variables may be set as part of a SELECT statement too.

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.