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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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
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
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.
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