how to write sql query with datediff greater than a value

harsha 21 Reputation points
2021-10-08T08:16:18.603+00:00

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

138784-image.png

Developer technologies Transact-SQL
{count} votes

Accepted answer
  1. Viorel 122.5K Reputation points
    2021-10-08T21:14:17.713+00:00

    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 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,221 Reputation points
    2021-10-08T08:27:18.873+00:00

    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.


  2. Yogesh Bhadauriya 26 Reputation points
    2021-10-08T08:42:55.053+00:00

    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
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.