How to get data by datetime logic in sql

Question

I have date(dt col name) column in est. If i execute query between '7:30pm'EST and '11:59' Est timezone.i should get previous day information and if i execute after 12:00 i need to get today's information.

I have tried this:

Case when getdate() between '2021-03-09 7:30:00' and '2021-03-09 11:59:00'
Then dt=getdate() -1 
else dt=getdate()

`GETDATE()` returns a `datetime` not a `date`. Also, `CASE` is an **expression**, it returns a scalar value. `Then dt=getdate() -1` doesn't make any sense; `dt=getdate() -1` isn't scalar value. — Thom A, Mar 09 '21 at 13:28
`IF` is a logical flow operator, you can't use it in a something like a `WHERE`, or `SELECT`. — Thom A, Mar 09 '21 at 13:46
@Userabc No one can read your mind and give you suggestions about how to accomplish an undefined goal. Posting a non-working script **fragment** is not particularly useful in understanding your issue. And you throw a reference to a specific timezone but no one knows how that relates directly to your logic. You need to provide more information and more context. — SMor, Mar 09 '21 at 15:23

score 0 · Answer 1 · answered Mar 09 '21 at 17:57

If I understand your question correctly, then you want to retrieve different rows depending on the time when the query is executed.

Using a case expression can work, but then you have to move the comparison with the case expression result outside the case expression.

So instead of:
... where case when <condition> then (Column = Value1) else (Column = Value2) end
do this:
... where Column = (case when <condition> then Value1 else Value2 end)
where the parentheses are added here for clarity.

Sample data

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');

Solution

Applying the construction from the start of my answer. Unit testing both options of the case expression will require a separate variable (or you could just wait hours and hours...).

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

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

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])

Your question also mentions the EST timezone. If you want the current execution time to be interpreted as EST, then have a look a this question and this documentation page. Whether you should store data in EST instead of UTC in the first place is a whole other question... You will end up with something like this.

-- converting the query reference time to EST
declare @referenceDate smalldatetime = (select getdate() at time zone 'Eastern Standard Time');

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;

Fiddle to see everything in action.

How to get data by datetime logic in sql

1 Answers1