Show time difference in minutes-CodePudding

I need to calculate the time differences on a given day. I tried something like that but not works.

CONVERT(TIME, DATEADD(MINUTE, DATEDIFF(MI, MIN(CreatedOn),  
                      MAX(CreatedOn)), 0), 108) AS WorkingTime

Thanks guys

CodePudding user response：

You can try this way, since you don't show the error you get I don't know what detail you can have, but to get the time difference you can get it like this as I leave you here below

DECLARE @MaxDate DATETIME=DATEADD(HOUR,20,GETDATE()) --here you could get the maximum of your table separately

DECLARE @MinDate DATETIME=GETDATE() --here you could get the minimum of your table separately


SELECT DATEDIFF(MINUTE,@MinDate,@MaxDate) AS WorkingTime

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=09128de0d996e85a21992197b524a208

CodePudding user response：

From this column I need to calculate the working time for each day, for this I need to select the first time on a given day and then round it to the full hour (06:08:20 to 06:00:00) and then calculate the differences between the last time in day, which will give me the working time in minutes.

My column

CodePudding user response：

Your question is not entirely clear, but I think this is what you're trying to do.

SELECT CAST(WorkingTime AS DATE),
MIN(WorkingTime) AS min,
MAX(WorkingTime) AS max, 
DATEADD(HOUR, DATEDIFF(HOUR, 0, MIN(WorkingTime)), 0) AS truncatedmin,
DATEDIFF(MINUTE,DATEADD(HOUR, DATEDIFF(HOUR, 0, MIN(WorkingTime)), 0),MAX(WorkingTime)) AS [difference]
FROM YourTable
GROUP BY CAST(WorkingTime AS DATE)

The column called "difference" should show the number of minutes difference between the truncatedmin and max columns.