How to find Between Values [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

1	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

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

0	@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

@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

Latest Posts