I want to select last hour results-CodePudding

SELECT DISTINCT
ip.IPAddress,convert(varchar, dan.AuditDate, 100) As Date, ip.AuditAction,ip.Username,
(CASE 
WHEN CONCAT(k.FirstName, ' ', k.LastName) IS NULL or CONCAT(k.FirstName, ' ', k.LastName) = ' ' THEN k.Name
ELSE CONCAT(k.FirstName, ' ', k.LastName)
END) AS 'Name' 
FROM IPAddress ip
inner join user k on k.ID= ip.PerformerID
inner join audit dan on ip.IPAddress= dan.Value1 AND ip.Date = CAST(dan.AuditDate As Date) AND ip.AuditAction = dan.AuditStr AND ip.PerformerID = dan.PerformerID
inner join LoginMoreOne n on n.LUsername=ip.Username AND n.LUsername IS NOT NULL
WHERE LEN(ip.IPAddress)>8 and dan.AuditDate > DATEADD(HOUR, datediff(hour, 0, dateadd(mi, 30, GETDATE())), 0)
GROUP BY ip.IPAddress,ip.Date,ip.AuditAction,ip.Username,ip.PerformerID, k.FirstName,k.LastName,k.Name,CAST(dan.AuditDate As Date),dan.AuditDate

For example If I Execute this query at 2.30pm, I want the result from 1pm to 2pm, or if i execute this query at 3.10pm, I want the result from 2pm-3pm, and so on, How do I edit my where statement to be able get the desired result

CodePudding user response：

The expression dateadd(hour, datediff(hour, 0, getdate()), 0) will gives you date & time at current hour, with minutes, seconds = 0

-1 gives you the previous hour with 0 mins, 0 secs

    AuditDate >= dateadd(hour, datediff(hour, 0, getdate()) - 1, 0)
and AuditDate <  dateadd(hour, datediff(hour, 0, getdate()), 0)

CodePudding user response：

Instead

dan.AuditDate > DATEADD(HOUR, datediff(hour, 0, dateadd(mi, 30, GETDATE())), 0)

Use:

dan.AuditDate > DATEADD(HH, -1.5, GETDATE()) AND dan.AuditDate <= DATEADD(HH,-0.5, GETDATE())