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 something 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?
7条答案
按热度按时间hjzp0vay1#
jq6vz3qz2#
This will also work:
Although cyberkiwi's answer is very clever! =)
ghg1uchk3#
I needed to pull a date from the database and append 3:00 Pm to it. I did it this way
The result that it returned was
2017-10-01 15:00:00.000
. The date in the database is2017-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.nvbavucw4#
In case of just updating a particular part of the datetime you can use
SMALLDATETIMEFROMPARTS
like:In other cases it may be required to copy parts of datetime to other or update only certain parts of the datetime:
Refer SQL Server Date/Time related API references for more such functions
dy1byipe5#
Notes:
GETDATE() + X
is the equivalent ofDATEADD(DAY, X, GETDATE())
.DATEIME
to aDATE
and then back to aDATETIME
again sets the time to midnight i.e.00:00:00.000
.SET
andDECLARE
statements are unnecessary, but just in case it helps later, variables may be set as part of aSELECT
statement too.cclgggtu6#
The solution is to convert time datatype into datetime and add
The result is 2023-10-01 15:00:00.000
vtwuwzda7#
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.
Hope this helps someone.