How to Query for a Specific Date Range Excluding a Specific Time Range
I Have a Program That Auto-Run's with Windows Scheduler. What the Program Does Is Runs a Query for "Yesterdays" Results. .. It Then Filters the Results Not...
I have a program that auto-run's with windows scheduler. What the program does is runs a query for "Yesterdays" Results... it then filters the results not including anything in the specific time range. The problem I have right now is I have to specify the date in the query... How can I have the query automatically exclude the time (between 5:30 am and 6:15 am)
SELECT Store_Id, DM_Corp_Received_Date
FROM Register_Till_Count_Tb
WHERE (DM_Corp_Received_Date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0))
AND (Register_Transaction_Type = 'sod')
AND (DM_Corp_Received_Date < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND (DM_Corp_Received_Date NOT BETWEEN CONVERT(datetime, '2012-08-08 05:30:00', 102) AND CONVERT(Datetime, '2012-08-08 06:15:00', 102))
1 Answer
DECLARE @Yesterday DATETIME = DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0));
DECLARE @Today DATETIME = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0));
SELECT Store_Id, DM_Corp_Received_Date
FROM Register_Till_Count_Tb
WHERE (DM_Corp_Received_Date >= @Yesterday
AND (Register_Transaction_Type = 'sod')
AND (DM_Corp_Received_Date < @Today
AND (DM_Corp_Received_Date NOT BETWEEN DATEADD(minute, 5*60+30, @Yesterday) AND
DATEADD(minute, 6*60+15, @Yesterday))
Note that BETWEEN is inclusive so be careful around your boundaries.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
