CODEWITHSUNDEEP
sqlgetdate
user6363065
user6363065

Reputation: 81

last hour(not add -1hr) data in sql

I am trying to get last hour data such a that, if getdate() is returning me '2016-11-14 13:09:09.653' then i want data from '2016-11-14 11:00:00.000' to '2016-11-14 12:00:00.000' not just 12:09 to 01:00.

Upvotes: 1

Views: 1356

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

Another option (assuming SQL Server 2012+)

Select Format(DateAdd(HH,-2,GetDate()),'yyyy-MM-dd HH:00:00')
      ,Format(DateAdd(HH,-1,GetDate()),'yyyy-MM-dd HH:00:00')

Returns 

(No column name)      (No column name)
2016-11-14 12:00:00   2016-11-14 13:00:00

EDIT Illustration No Need to Recast

Declare @Table table (SomeDateTime datetime)
Insert Into @Table values 
('2016-11-14 12:15:00'),
('2016-11-14 13:30:00')

Select * 
 From  @Table
 Where SomeDateTime Between Format(DateAdd(HH,-2,GetDate()),'yyyy-MM-dd HH:00:00') 
                        and Format(DateAdd(HH,-1,GetDate()),'yyyy-MM-dd HH:00:00')

Returns

2016-11-14 12:15:00.000

Upvotes: 2

Matt
Matt

Reputation: 14381

SELECT *
FROM
   TableName
WHERE
   BETWEEN

    DATEADD(HOUR,DATEPART(HOUR,GETDATE()) - 2,CAST(CAST(GETDATE() AS DATE) AS DATETIME))

  AND

    DATEADD(HOUR,DATEPART(HOUR,GETDATE()) - 1,CAST(CAST(GETDATE() AS DATE) AS DATETIME))

Upvotes: 2

Related Questions