Dynamics Date filtering/Capturing

,
1

Hi, how can I capture the records from 6:00AM of previous date and 6:00AM of current date or it can be dynamic date filtering but the timestamp should be always at 6:00AM. any help is very much appreciated. thank you.

see below sample data.

[code]--declare table
declare @s1 table (line nvarchar(10), po_created datetime)
--populate records
insert @s1 values ('L1','2016-08-09 01:32:08.000')
insert @s1 values ('L1','2016-08-09 23:23:05.000')
insert @s1 values ('L1','2016-08-09 20:06:37.000')
insert @s1 values ('L1','2016-08-09 11:40:03.000')
insert @s1 values ('L1','2016-08-09 08:20:06.000')
insert @s1 values ('L1','2016-08-09 11:02:32.000')
insert @s1 values ('L1','2016-08-09 07:13:42.000')
insert @s1 values ('L2','2016-08-10 03:44:25.000')
insert @s1 values ('L2','2016-08-10 05:22:09.000')
insert @s1 values ('L2','2016-08-10 06:41:53.000')
insert @s1 values ('L2','2016-08-10 16:37:11.000')
insert @s1 values ('L2','2016-08-10 20:22:36.000')
insert @s1 values ('L2','2016-08-10 21:54:50.000')
insert @s1 values ('L2','2016-08-10 20:07:50.000')
insert @s1 values ('L2','2016-08-10 17:31:32.000')
insert @s1 values ('L2','2016-08-10 22:14:28.000')
insert @s1 values ('L2','2016-08-10 13:07:06.000')
insert @s1 values ('L2','2016-08-10 11:29:45.000')
insert @s1 values ('L2','2016-08-10 03:42:29.000')
insert @s1 values ('L2','2016-08-10 05:03:06.000')
insert @s1 values ('L2','2016-08-10 01:42:34.000')
insert @s1 values ('L1','2016-08-10 06:59:48.000')
insert @s1 values ('L1','2016-08-11 08:58:36.000')
insert @s1 values ('L1','2016-08-11 08:34:20.000')
insert @s1 values ('L1','2016-08-11 10:42:57.000')
insert @s1 values ('L1','2016-08-11 11:14:49.000')
insert @s1 values ('L1','2016-08-11 15:47:23.000')
insert @s1 values ('L1','2016-08-12 02:28:36.000')
insert @s1 values ('L1','2016-08-12 06:40:50.000')

select
line, po_created
from @s1
where po_created between '2016-8-11 6:00 AM' AND '2016-8-12 6:00 AM'

desired result

Line------------2016-8-9------2016-8-10-----2016-8-11

L1 ------------6--------------1------------6
L2 ------------5--------------9------------

Raw data

--for 8/9/2016 ( 8/9/2016 06:00AM to 8/10/2016 06:00AM)
line po_created

L1 2016-08-09 07:13:42.000
L1 2016-08-09 08:20:06.000
L1 2016-08-09 11:02:32.000
L1 2016-08-09 11:40:03.000
L1 2016-08-09 20:06:37.000
L1 2016-08-09 23:23:05.000
L2 2016-08-10 01:42:34.000
L2 2016-08-10 03:42:29.000
L2 2016-08-10 03:44:25.000
L2 2016-08-10 05:03:06.000
L2 2016-08-10 05:22:09.000

--for 8/10/2016 ( 8/10/2016 06:00AM to 8/11/2016 06:00AM)
line po_created

L2 2016-08-10 06:41:53.000
L1 2016-08-10 06:59:48.000
L2 2016-08-10 11:29:45.000
L2 2016-08-10 13:07:06.000
L2 2016-08-10 16:37:11.000
L2 2016-08-10 17:31:32.000
L2 2016-08-10 20:07:50.000
L2 2016-08-10 20:22:36.000
L2 2016-08-10 21:54:50.000
L2 2016-08-10 22:14:28.000

--8/11/2016 ( 8/11/2016 06:00AM to 8/12/2016 06:00AM)
line po_created

L1 2016-08-11 08:34:20.000
L1 2016-08-11 08:58:36.000
L1 2016-08-11 10:42:57.000
L1 2016-08-11 11:14:49.000
L1 2016-08-11 15:47:23.000
L1 2016-08-12 02:28:36.000[/code]

2 
DECLARE @start_date datetime
DECLARE @num_of_days_to_list int
SET @start_date = DATEADD(HOUR, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
SET @num_of_days_to_list = 1

SELECT *
FROM table_name
WHERE po_created >= @start_date AND 
    po_created < DATEADD(DAY, @num_of_days_to_list, @start_date)
3

Thank you scott