How to find Between Values [Resolved]

Posted by Jayakumars under Sql Server on 2/1/2016 | Points: 10 | Views : 475 | Status : [Member] [MVP] | Replies : 2
Hi

How to find hours between dates

My Table data this

--Date
--2016-01-31 14:47:20.650
--2016-01-30 14:47:20.650
--2016-01-29 14:47:20.650
--2016-01-28 14:47:20.650
--2016-01-27 14:47:20.650
--2016-01-26 14:47:20.650

here how to compare current date and time to compare above date .
i need return how many hours between each rows.

Mark as Answer if its helpful to you


Responses

Posted by: Professionaluser on: 2/2/2016 [Member] [MVP] Bronze | Points: 50

Up
1
Down

Resolved
Hi Jayakumar,

find the below solution which suits your requirement...


declare @Tab table (Dt DATETIME)
insert @Tab
SELECT '2016-01-31 14:47:20.650' union all
SELECT '2016-01-30 14:47:20.650' union all
SELECT '2016-01-29 14:47:20.650' union all
SELECT '2016-01-28 14:47:20.650' union all
SELECT '2016-01-27 14:47:20.650' union all
SELECT '2016-01-26 14:47:20.650'

;with CTE as ( SELECT Dt, ROW_NUMBER() OVER(ORDER BY (select 1)) RN from @Tab)
SELECT c1.Dt CurrentDatetime, DATEDIFF(HH, c1.Dt, ISNULL(c2.Dt, c1.Dt)) DiffHours, c2.Dt NextDatetime
FROM CTE c1
LEFT JOIN CTE c2 on c1.RN+1 = C2.RN


Output:

CurrentDatetime DiffHours NextDatetime
2016-01-31 14:47:20.650 -24 2016-01-30 14:47:20.650
2016-01-30 14:47:20.650 -24 2016-01-29 14:47:20.650
2016-01-29 14:47:20.650 -24 2016-01-28 14:47:20.650
2016-01-28 14:47:20.650 -24 2016-01-27 14:47:20.650
2016-01-27 14:47:20.650 -24 2016-01-26 14:47:20.650
2016-01-26 14:47:20.650 0 NULL



if you want DiffHours in positive values, use this code DATEDIFF(HH, ISNULL(c2.Dt, c1.Dt), c1.Dt) for DiffHours column

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 2/1/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
@Jayakumars, so the query that you are looking for is to find the difference in hours from the given date and time and Current Date and Time. Here you go

DECLARE @T TABLE(Dt datetime)
INSERT INTO @T VALUES
('2016-01-31 14:47:20.650'),('2016-01-30 14:47:20.650'),
('2016-01-29 14:47:20.650'),('2016-01-28 14:47:20.650'),
('2016-01-27 14:47:20.650'),('2016-01-26 14:47:20.650')

SELECT
Dt,
CurrentDT = GETDATE(),
DATEDIFF(HH ,Dt, GETDATE()) HrsDiff
FROM @T


Hope that helps

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response