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:
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?
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.%'
)
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
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.
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?
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?
@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!
