SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table MyTable
(
  dt date,
  data nvarchar(20)
);

insert into MyTable (dt, data) values
('2021-03-08', 'yesterday''s data'),
('2021-03-09', 'today''s data');

select * from MyTable;


dt
data


2021-03-08
yesterday's data

2021-03-09
today's data


-- requesting data on today's date in period [07:30, 11:59]

declare @referenceDate smalldatetime = '2021-03-09 10:20';
--> using date variable instead of 'getdate()' for demo purposes !

select mt.dt,
       mt.data
from MyTable mt
where mt.dt = case
                when @referenceDate between '2021-03-09 07:30:00' and '2021-03-09 11:59:00'
                then dateadd(day, -1, convert(date, @referenceDate))
                else convert(date, @referenceDate)
              end;
--> returns data from yesterday


dt
data


2021-03-08
yesterday's data


-- requesting data on today's date outside period [07:30, 11:59]

declare @referenceDate smalldatetime = '2021-03-09 17:40';
--> using date variable instead of 'getdate()' for demo purposes !

select mt.dt,
       mt.data
from MyTable mt
where mt.dt = case
                when @referenceDate between '2021-03-09 07:30:00' and '2021-03-09 11:59:00'
                then dateadd(day, -1, convert(date, @referenceDate))
                else convert(date, @referenceDate)
              end;
--> returns data from today


dt
data


2021-03-09
today's data


-- data prep

truncate table MyTable;
insert into MyTable (dt, data) values
(dateadd(day, -1, convert(date, getdate())), 'yesterday'),
(convert(date, getdate()), 'today');

-- requesting data on _any_ date depending on period [07:30, 12:00]

select mt.dt,
       mt.data
from MyTable mt
where mt.dt = case
                when convert(time, getdate()) >= '07:30:00'
                 and convert(time, getdate()) <  '12:00:00'
                then dateadd(day, -1, convert(date, getdate()))
                else convert(date, getdate())
              end;
--> returns data from today OR yesterday depeding on time of execution (in/outside [07:30, 12:00])


dt
data


2021-03-09
today


-- requesting data on _any_ date depending on period [07:30, 12:00]

-- converting the query reference time to EST (https://en.wikipedia.org/wiki/Eastern_Time_Zone)
declare @referenceDate smalldatetime = (select getdate() at time zone 'Eastern Standard Time');

-- comparing between UTC time
select convert(smalldatetime, getdate()) as 'UTC',
       @referenceDate as 'EST';

select mt.dt,
       mt.data
from MyTable mt
where mt.dt = case
                when convert(time, @referenceDate) >= '07:30:00'
                 and convert(time, @referenceDate) <  '12:00:00'
                then dateadd(day, -1, convert(date, @referenceDate))
                else convert(date, @referenceDate)
              end;


UTC
EST


2021-03-09 17:54
2021-03-09 17:54




dt
data


2021-03-09
today