SQLTeam.com | Weblogs | Forums

BETWEEN DATEADD(Hour , -1, GETDATE()) AND GETDATE() , providing Incorrect Data

I am doing a query in which I want the last one hour data. [E:g: StartTime is the DateTime column of format (MM/dd/yyyy HH:mm:ss ).First, I am converting the StartTime to a different Timezone AS RunTime.

To find last 1 hour data followed by one other Logic to filter, I tried using the below conditions:

  1. WHERE StartTime BETWEEN DATEADD(HOUR, -1, GETDATE()) AND GETDATE()
    AND info like 'Could not start.%'
    OR WHERE StartTime = DATEADD(HOUR, -1, GETDATE())
    AND info like 'Account locked.%'
    If I execute the query at 8pm, I want the last hour data from 7-8pm , but this returns result from 4pm, 3pm also.
    Am I expecting the right result?

Looks like you are missing parenthesis. Try this

WHERE (
StartTime BETWEEN DATEADD(HOUR, -1, GETDATE()) AND GETDATE()
AND info like 'Could not start.%'
)
OR (
WHERE StartTime = DATEADD(HOUR, -1, GETDATE())
AND info like 'Account locked.%'
)

Thank you for the Response Mike, I tried with the parenthesis now and Getting the same datas from 5Pm, when I executed at 8pm

Try this condition for the Date column.

StartTime = DATE(NOW()) AND StartTime > DATEADD(HOUR, -1, GETDATE())

Date(NOW()) seems to be incorrect syntax . Should I enter SYSDATETIME() Instead? I'm using ms sql

Can you post some DDL and sample data? Not sure why you would be returning 5pm for StartTime column.

Sure, I have a table -Jobs with Column ( StartTime(GMT), Process, Info). I am first converting StartTime to EST Timezone, Alias AS RunTime
if Info = 'Could not start Executor', I need to Get the last 1 hour process and Info data.

Please find the query below:

SELECT distinct CONVERT(datetime2(0),SWITCHOFFSET(StartTime,0) AT TIME ZONE 'Eastern Standard Time') AS Runtime
  ,CONVERT(datetime2(0),SWITCHOFFSET(EndTime,0) AT TIME ZONE 'Eastern Standard Time') AS CompletedTime
    J.[State]
      ,J.[Info]
  ,Robots.UserName
  ,Releases.[Name] AS ProcessName
  ,Robots.[Name] AS RobotName
  ,T.[TenancyName]
  ,J.[HostMachineName]
  ,J.TenantId
  FROM Jobs as J
  JOIN Tenants as T
      ON J.TenantId = T.Id
  Left JOIN Releases as Releases
  ON J.ReleaseId= Releases.Id
  Left JOIN Robots as Robots
  ON J.RobotId= Robots.Id
      where StartTime BETWEEN DATEADD(HOUR,-1, GETDATE()) AND GETDATE()
  AND Info like 'Could not start executor.%'
  OR  StartTime BETWEEN DATEADD(HOUR,-1, GETDATE()) AND GETDATE()
  AND Info like 'The referenced account is currently locked out and may not be logged on to.%'
  order by Runtime desc

that is not ddl nor sample data. Please provide easily consumable data

create table A (Field1 int......)

insert into table A values (......)

Create table Jobs(
StartTime Datetime,
EndTime Datetime,
State Int,
Info nvarchar,
TenantId int,
ReleaseId int)

Create table Releases(
id int,
Name nvchar)

Create table Robots(
id int,
Name nvchar)

Sample data:

StartTime > MM/dd/yyyy HH:mm:ss
Info> " Could not start Executor .
ProcessName> MessageBox
RobotName> BOTA

If StartTime BETWEEN DATEADD(HOUR,-1, GETDATE()) AND GETDATE()
AND Info like 'Could not start executor.%', when I execute the query at 2AM I expect the result to be :

StartTime> 08/13/2021 1:05:33
Info> "Could not start"
ProcessName> MessageBox
RobotName> BOTA

The difference between GMT to EST is 4 hours, For example: If the result occurred at 9.30Pm and when I execute this query at that time, its not returning the result , but if I execute it at 1:30 am, that is getting returned.

Will difference in Timezone cause the conflict?

Difference in Time Zone = CAN cause conflict

if you want to do something with date1 and date2 like are they both equal
both should be in the same time zone

convert ( date1 into timezone123 ) = convert ( date2 into timezone123)

Hi Harish, I have converted the Time in the beginning my query using :
CONVERT(datetime2(0),SWITCHOFFSET(StartTime,0) AT TIME ZONE 'Eastern Standard Time') AS Runtime
This is the Date 1 post which I am filtering this,
where StartTime BETWEEN DATEADD(HOUR,-1, GETDATE()) AND GETDATE()
Should I add anything in the filtration part to break the conflict?

you are using GETDATE .... please check the time zone of GETDATE

-- prgramming is all St... things like this only .. commas full stops

1 Like

GETDATE Is the Current System Time- EST Time Zone

one thing i do to debug .. is

what is the starting ... the parts you are adding .. and the final result

example

if i do
cast(date1 as something) + date2

the way i debug is

select date1 , date 2 , cast(date1 as something) , cast(date1 as something) + date2

that way i can see the individual parts and where its going wrong

1 Like

I get it, I did not think of GETDATE () . I was checking other Dates on what got wrong.Thank you.
But Does it mean I have to change the entire query?
Is it possible to Add anything in that line for GETDATE to be in Same Time Zone track?

I tried with where RunTime BETWEEN DATEADD(HOUR,-1, GETDATE()) AND GETDATE(), but the Query is not allowing me to use RunTime(Alias for Converted EST Time Zone).
RunTime Alias cannot be used in the Where Clause , is there anyway to solve this conflict?

one solution
CTE

; with cte as ( select query with alias )
select * from cte where alias --> here use the alias

what i mean is .. create a temp table with the alias

in another seperate query use the temp table with where clause using the alias

1 Like

I am trying your Solution! ill inform

@harishgg1 IT was a Delight and worked perfectly! Since I am not aware of CTE, I wrapped the First set of Query with new table, and then used another separate query. Thank you so much for the Solution!

you are Welcome Radha

I have plenty of experience in t-sql

Always so many NEW things i face .. where its very difficult to make sense
And the BOSS wants it done in 1 hour

:+1: :+1: