how to write sql query with datediff greater than a value

Question

question

harsha-9542 asked Oct 8, '21 Viorel-1 commented Nov 21, '21

how to write sql query with datediff greater than a value

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
1. calculate the time difference between start date and end date
2. 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

sql-server-transact-sql

image.png (6.0 KiB)

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach · Oct 08, 2021 at 10:40 PM

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.

0 ·

harsha-9542 pituach · Nov 21, 2021 at 01:09 PM

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

0 ·

MelissaMa-msft · Oct 11, 2021 at 02:32 AM

Hi @harsha-9542,

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

0 ·

Answer 1 · 2021-10-08T21:14:17.713Z

Viorel-1 answered Oct 8, '21 Viorel-1 commented Nov 21, '21

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

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

harsha-9542 · Nov 21, 2021 at 01:04 PM

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.

0 ·

Viorel-1 harsha-9542 · Nov 21, 2021 at 02:24 PM

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.

0 ·

Answer 2 · 2021-10-08T08:27:18.873Z

MelissaMa-msft answered Oct 8, '21 harsha-9542 commented Nov 21, '21

Hi @harsha-9542,

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.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach · Oct 08, 2021 at 10:42 PM

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

0 ·

harsha-9542 · Nov 21, 2021 at 01:23 PM

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

0 ·

Answer 3 · 2021-10-08T08:42:55.053Z

YogeshBhadauriya-4299 answered Oct 8, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question