Created a date range for hour and minutes of given datetime-CodePudding

SQL Server 2016 or newer

Is there a more concise way to do the following?

Basically, I'm given a datetime and I need to create a range based on the hour and minutes.

DECLARE @date_in DATETIME = '6/11/2022 7:14:47 PM'

-- Goal is to come up with a date range of the minute
-- In the above example:
-- ...where criteria_date between '6/11/2022 19:14:00' and '6/11/2022 19:15:00'

DECLARE @date_from DATETIME
DECLARE @date_to DATETIME
DECLARE @hourMinutesFrom VARCHAR(5)
DECLARE @hourMinutesTo VARCHAR(5)

SELECT @hourMinutesFrom = CONVERT(VARCHAR(5),@date_in,108) 
SELECT @hourMinutesTo = CONVERT(VARCHAR(5),DATEADD(MINUTE,1,@date_in),108) 

SELECT @date_from = DATEADD(day, DATEDIFF(day, 0, @date_in), @hourMinutesFrom)
SELECT @date_to = DATEADD(DAY, DATEDIFF(DAY, 0, @date_in), @hourMinutesTo)

SELECT @date_from
SELECT @date_to

CodePudding user response：

In SQL Server 2022 (which is in preview), you could use DATE_BUCKET:

DECLARE @date datetime = '2022-11-06T19:14:47'; --Don't use ambiguous date formats

SELECT {Columns}
FROM dbo.YourTable
WHERE criteria_date BETWEEN DATE_BUCKET(MINUTE,1,@date) AND DATEADD(MINUTE,1,DATE_BUCKET(MINUTE,1,@date));

Note that due to your use of BETWEEN this will include '2022-11-06T19:15:00' but not '2022-11-06T19:15:01'.

On older version, then you would need to use "messier" DATEADD/DATEDIFF logic and "magic" dates:

DECLARE @date datetime = '2022-11-06T19:14:47'; --Don't use ambiguous date formats

SELECT {Columns}
FROM dbo.YourTable
WHERE criteria_date BETWEEN DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @Date),0) AND DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @Date) 1, 0);

If you don't want exactly '2022-11-06T19:15:00' then use >= and < logic rather than BETWEEN. DATE_BUCKET is also sort preserving, so you you may find the following is SARGable too (I don't have a 2022 instance at home yet to test this, due to lack of the Linux release):

DECLARE @date datetime = '2022-11-06T19:14:47'; --Don't use ambiguous date formats

SELECT {Columns}
FROM dbo.YourTable
WHERE DATE_BUCKET(MINUTE,1,criteria_date) = DATE_BUCKET(MINUTE,1,@date);