how to write sql query with datediff greater than a value

Question

how to write sql query with datediff greater than a value

harsha 21

Hello There,

I have table called logs and I have few columns in that along with StartDate and EndDate. I am trying to write a query that should

calculate the time difference between start date and end date
I want to display the result whose time difference is greater than 1 hours.

I have startdate and enddate column in YYYY-MM-DD HH:MM:SS:MS format. I have written query to calculate time difference between startdate and end date but unable to write a condition to calculate if that time difference is above 1 hr

My query is below

select StartDate, EndDate,
CONCAT((DATEDIFF(Minute,StartDate,EndDate)/60),':',
(DATEDIFF(Minute,StartDate,EndDate)%60)) TimeTaken ,

from Logs with(nolock)
where
StartDate!=EndDate

I see the result as below. But I want to add one more condition like I want to display the records where time taken is greater than 1 hour

Ronen Ariely 15,206 Reputation points

2021-10-08T22:40:23.383+00:00

I see the result as below. But I want to add one more condition like I want to display the records where time taken is greater than 1 hour

Seems like you solve all you need except the filter (WHERE) condition.

You can use for the filter something like WHERE DATEDIFF(hour,startdate,enddate)>1

Note! The problem is that using this filter might result with poor performance, since no index seek will be able to used. Using functions like this, the server must scan all option in order to check if the output of the function fit the condition or not.

If this requirement is somethin that you use every time that use the data, then you might want to add a computed PERSISTED column as DATEDIFF(Minute,sd,Ed) and create index on that column. In this case in the filter you can use >60 and you can use this column to present the result.
MelissaMa-MSFT 24,221 Reputation points

2021-10-11T02:32:41.837+00:00

Hi @harsha ,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa
harsha 21 Reputation points

2021-11-21T13:09:26.843+00:00

hi pituach.. thanks for taking time and responding... based on the query you shared I have to re execute datediff on startdate and enddate columns... I mean I already got the time difference using below

CONCAT((DATEDIFF(Minute,StartDate,EndDate)/60),':',
(DATEDIFF(Minute,StartDate,EndDate)%60)) TimeTaken ,

and instead of calculating time difference once again using datediff if I can use the time taken value column which is already calculated the query execution will be faster.

And also when I give the value in decimal (>1.3, >2.4 etc)as below I am not getting accurate results

WHERE DATEDIFF(hour,startdate,enddate)>1.3

Accepted answer

2 additional answers

Your answer

Ronen Ariely 15,206 Reputation points

2021-10-08T22:40:23.383+00:00

I see the result as below. But I want to add one more condition like I want to display the records where time taken is greater than 1 hour

Seems like you solve all you need except the filter (WHERE) condition.

You can use for the filter something like WHERE DATEDIFF(hour,startdate,enddate)>1

Note! The problem is that using this filter might result with poor performance, since no index seek will be able to used. Using functions like this, the server must scan all option in order to check if the output of the function fit the condition or not.

If this requirement is somethin that you use every time that use the data, then you might want to add a computed PERSISTED column as DATEDIFF(Minute,sd,Ed) and create index on that column. In this case in the filter you can use >60 and you can use this column to present the result.
MelissaMa-MSFT 24,221 Reputation points

2021-10-11T02:32:41.837+00:00

Hi @harsha ,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa
harsha 21 Reputation points

2021-11-21T13:09:26.843+00:00

hi pituach.. thanks for taking time and responding... based on the query you shared I have to re execute datediff on startdate and enddate columns... I mean I already got the time difference using below

CONCAT((DATEDIFF(Minute,StartDate,EndDate)/60),':',
(DATEDIFF(Minute,StartDate,EndDate)%60)) TimeTaken ,

and instead of calculating time difference once again using datediff if I can use the time taken value column which is already calculated the query execution will be faster.

And also when I give the value in decimal (>1.3, >2.4 etc)as below I am not getting accurate results

WHERE DATEDIFF(hour,startdate,enddate)>1.3

Answer 1

Viorel 122.6K

To simplify things, check these stuffs too:

select StartDate, EndDate,
    concat(format(d / 60, '0'), ':', format(d % 60, '00')) as TimeTaken
from Logs
cross apply (values (datediff(minute, StartDate, EndDate))) D(d)
where d > 60

harsha 21 Reputation points

2021-11-21T13:04:34.143+00:00

Thanks Viorel I am getting more accurate results with above query!!

can you help me understand what is being done under

cross apply (values (datediff(minute, StartDate, EndDate))) D(d)

and what does D indicates here.
Viorel 122.6K Reputation points

2021-11-21T14:24:17.947+00:00

It creates an imaginary variable 'd = datediff(minute, StartDate, EndDate)' for each row, which can be then used multiple times, so that you do not have to repeat this datediff. It is like a column of a table, which was called D. You can choose different names for D and d.

Answer 2

MelissaMa-MSFT 24,221

Hi @harsha ,

Welcome to Microsoft Q&A!

You could try with DATEDIFF function which could calculate the time difference.

Please refer to below:

select StartDate, EndDate,  
CONCAT((DATEDIFF(Minute,StartDate,EndDate)/60),':',  
(DATEDIFF(Minute,StartDate,EndDate)%60)) TimeTaken  
from Logs with(nolock)  
where StartDate!=EndDate  
and DATEDIFF(hour,startdate,enddate)>1

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Ronen Ariely 15,206 Reputation points

2021-10-08T22:42:06.627+00:00

If DATEDIFF(hour,StartDate,EndDate )>1 then it is a must that StartDate!=EndDate so we can remove this part of the condition :-)
harsha 21 Reputation points

2021-11-21T13:23:14.607+00:00

hi Melissa thanks for taking time and replying me.. based on above query I need to re excute datediff which will be an overhead though I have the time taken value available... and also if I use decimal values I am not getting accurate results

and DATEDIFF(hour,startdate,enddate)>1.3
and DATEDIFF(hour,startdate,enddate)>1.4
and DATEDIFF(hour,startdate,enddate)>2.4

Answer 3

Yogesh Bhadauriya 26

I am suggesting you to use DATEDIFF function with Minute Parameter.

DECLARE @StartTime DATETIME='2021-10-08 10:55:00'
DECLARE @EndTime DATETIME='2021-10-08 11:15:00'

---DATEDIFF WITH HOUR, GIVE Only Hour Difference irespective of time duration
SELECT DATEDIFF(HOUR,@StartTime,@EndTime)

-----OUTPUT
--1

---DATEDIFF WITH MINUTE, will give you accurate result
SELECT DATEDIFF(MINUTE,@StartTime,@EndTime)

-----OUTPUT
--20

Share via

how to write sql query with datediff greater than a value

2 additional answers

Your answer