Calculate Datetime Amount in HH: MM: SS in sql

Question

I want to calculate the amount in HH: MM: SS

First I calculate datetime difference in HH: MM: SS (works well)

Select * Into #_TicketType From ( Select *, convert(varchar(5), DateDiff(s, [Start Date] , [End Date] )/3600) + ':' + convert(varchar(5),DateDiff(s, [Start Date] , [End Date])%3600/60) + ':' + convert(varchar(5),(DateDiff(s,[Start Date] , [End Date])%60)) as [Ticket Type Time] , convert(varchar(5),DateDiff(s, [Ticket Open Time] , [Ticket Closed Time] )/3600) + ':' + convert(varchar(5),DateDiff(s,[Ticket Open Time] , [Ticket Closed Time])%3600/60) + ':' + convert(varchar(5),(DateDiff(s,[Ticket Open Time] , [Ticket Closed Time])%60))[Total Call Time] FROM CTE )_A 

[Start Date] , [End Date],[Ticket Open Time] , [Ticket Closed Time] columns of DateTime type

Next, I want to calculate the sum of it in HH: MM: ss

My request: -

 Select Distinct ATM, CAST ( (SUM (datepart(hh, convert (varchar, [Ticket Type Time], 108))) + (sum (datepart(mi, convert (varchar, [Ticket Type Time], 108)))/60) ) AS VARCHAR(2)) + ':' + CAST ( sum( datepart(mi, convert (varchar, [Ticket Type Time], 108))) - 60 * (sum(datepart(mi, convert (varchar, [Ticket Type Time], 108)))/60) as VARCHAR(2)) + ':' + CAST ( sum( datepart(ss, convert (varchar, [Ticket Type Time], 108))) - 60 * (sum( datepart(SS, convert (varchar, [Ticket Type Time], 108)))/60) as VARCHAR(2)) From #_TicketType Group By ATM 

It gives me an error

 **Msg 241, Level 16, State 1, Line 67 Conversion failed when converting date and/or time from character string.** 

SQL Server 2008 Database Date

Answer 1

I think you wanted to convert at time (or datetime if your version of SQL Server does not support it), not varchar.

But this is a very bad way to do it. 25:01:01 will not be a valid date. You should summarize the seconds (as suggested in another answer) and then format the results as you see fit at the end.

So, in your first query, include another column, the duration of which in seconds:

 , DateDiff(s,[Start Date] , [End Date]) as [Ticket Type Seconds] 

Then, in the second request, simply format the SUM ([Ticket Type Seconds]), perhaps like this:

 STR(SUM([Ticket Type Seconds])/3600) + RIGHT(CONVERT(char(8),DATEADD(s,SUM([Ticket Type Seconds]),0),108),6) 

This gives you a clock (which can be more than 24) connected to the ": MM: SS" part in the time date that you get when you convert seconds (added to the date 0: 19000101 00:00: 00) to datetime.

Answer 2

There are two related issues here.

• SQL Server does not support Interval data types.
• “8 o’clock” and “8 o’clock” are often written in the same way - “8:00:00” - but they mean completely different things.

---

You cannot add values ​​of type "DateTime". Does "8 hours plus 3 hours" make sense? No.

Now "8 hours plus 3 hours" makes sense, but the clock is not of the DateTime type. In standard SQL, they are of type Interval. On systems that do not support the Interval data type - one of them is SQL Server, you need to calculate the difference in known units, for example, hours, minutes or seconds. You can easily summarize these values.

• Calculate the difference in seconds or minutes.
• Keep these values ​​in the query.
• Enter the number of seconds or minutes.
• Take the conversion to a convenient, convenient display format (HH: MM: SS) as a separate step, or perhaps do it for the front-end application code.

This should eliminate some or all of the calls to convert () and possibly even work faster.