Menu
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?
671
SQL Server DATEADD() Function The DATEADD() function adds a time/date interval to a date and then returns the date.
There are two ways to initialize the DateTime variable: DateTime DT = new DateTime();// this will initialze variable with a date(01/01/0001) and time(00:00:00). DateTime DT = new DateTime(2019,05,09,9,15,0);// this will initialize variable with a specific date(09/05/2019) and time(9:15:00).
To declare a date variable, use the DECLARE keyword, then type the @variable_name and variable type: date, datetime, datetime2, time, smalldatetime, datetimeoffset. In the declarative part, you can set a default value for a variable. The most commonly used default value for a date variable is the function Getdate().
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! =)
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
