CODEWITHSUNDEEP
sqlsql-server
Virender
Virender

Reputation: 51

Find Difference in hour by passing date filter

This is my Table data :

ID          FromDate                    ToDate
1           2018-12-07 12:25:47.000     2018-12-07 18:28:44.150
2           2018-12-07 18:28:44.150     2018-12-07 22:33:11.867
3           2018-12-07 22:33:11.867     2018-12-08 03:14:16.630
4           2018-12-08 03:14:16.630     2018-12-09 02:10:44.723

This is my query to fetch hour difference

Select DATEDIFF(HH,FromDate,TODate) As Hour,* from Test Where FromDate Between '2018-12-07 12:25:47.000' And '2018-12-08 12:25:47.000'
or ToDate Between '2018-12-07 12:25:47.000' And '2018-12-08 12:25:47.000'

Select DATEDIFF(HH,FromDate,TODate) As Hour,* from Test Where FromDate Between '2018-12-08 12:25:47.000' And '2018-12-09 12:25:47.000'
or ToDate Between '2018-12-08 12:25:47.000' And '2018-12-09 12:25:47.000'

My Output For 1st query should be

6
4
5
9

And for 2nd Query output should be 

14

Reason : My Fourth data comes under both query output but i only need hour difference between my date range which i am passing in select query

Upvotes: 0

Views: 55

Answers (1)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

This should get you started:

declare @table table (id int ,fromdate datetime,todate datetime)
insert into @table
values

(1           ,'2018-12-07 12:25:47.000','2018-12-07 18:28:44.150'),
(2           ,'2018-12-07 18:28:44.150','2018-12-07 22:33:11.867'),
(3           ,'2018-12-07 22:33:11.867','2018-12-08 03:14:16.630'),
(4           ,'2018-12-08 03:14:16.630','2018-12-09 02:10:44.723')

declare @todate datetime = '2018-12-08 12:25:47.000'
declare @fromdate datetime = '2018-12-08 12:25:47.000'


Select case when @todate > todate then DATEDIFF(HH,FromDate,TODate) else
datediff(HH,fromdate,@todate) end As Hour,* from @table Where
( FromDate Between '2018-12-07 12:25:47.000' And '2018-12-08 12:25:47.000'
or  ToDate Between '2018-12-07 12:25:47.000' And '2018-12-08 12:25:47.000')

Select case when @fromdate > fromdate then DATEDIFF(HH,@fromdate,TODate) else datediff(HH,fromdate,todate) end As Hour,* from @table Where FromDate Between '2018-12-08 12:25:47.000' And '2018-12-09 12:25:47.000'
or ToDate Between '2018-12-08 12:25:47.000' And '2018-12-09 12:25:47.000'

Upvotes: 1

Related Questions