Check if given date fits between a range of dates/time

Transact-SQL
, ,
1

Hi, I'm looking a script the find the date the falls within the date range/time. below is a sample data . The time range is 6:00AM of current date and 6:00AM of next Day. Thank you.

[code]declare @begdate datetime
declare @enddate datetime
set @begdate ='2016-10-04 06:00AM'
set @enddate ='2016-10-05 06:00AM'

DECLARE @Sample TABLE
(
transaction_date datetime
)
INSERT @Sample
VALUES ('2016-10-04 06:01:00.000'),
('2016-10-04 06:57:00.000'),
('2016-10-04 07:17:00.000'),
('2016-10-04 08:03:00.000'),
('2016-10-04 09:12:00.000'),
('2016-10-04 12:57:00.000'),
('2016-10-04 17:57:00.000'),
('2016-10-04 19:17:00.000'),
('2016-10-04 08:03:00.000'),
('2016-10-04 23:12:00.000'),
('2016-10-05 03:57:00.000'),
('2016-10-05 05:57:00.000'),

('2016-10-05 06:17:00.000'),
('2016-10-05 08:03:00.000'),
('2016-10-05 19:12:00.000')

select *
from @Sample

Sample -------------------desire result
2016-10-04 06:01:00.000----2016-10-04
2016-10-04 06:57:00.000----2016-10-04
2016-10-04 07:17:00.000----2016-10-04
2016-10-04 08:03:00.000----2016-10-04
2016-10-04 09:12:00.000----2016-10-04
2016-10-04 12:57:00.000----2016-10-04
2016-10-04 17:57:00.000----2016-10-04
2016-10-04 19:17:00.000----2016-10-04
2016-10-04 08:03:00.000----2016-10-04
2016-10-04 23:12:00.000----2016-10-04
2016-10-05 03:57:00.000----2016-10-04
2016-10-05 05:57:00.000----2016-10-04

2016-10-05 06:17:00.000----2016-10-05
2016-10-05 08:03:00.000----2016-10-05
2016-10-05 19:12:00.000----2016-10-05[/code]

2

Maybe?:

select transaction_date, cast(dateadd(day, case when datepart(hour, transaction_date) < 6 then -1 else 0 end, 
    transaction_date) as date) as transaction_day
from @Sample
3

Hi scott thank you for the reply. Kindly give me a little bit explanation how this code works.

cast(dateadd(day, case when datepart(hour, transaction_date) < 6 then -1 else 0 end, transaction_date) as date) as transaction_day

4

Code simply checks to see if the time of day is before 6AM. If it is, it adjusts the date listed to the previous day.

5

Thank you Scott