Can't convert time to mm:ss

can definition
can synonym
can noun meaning
can band
can verb
can y
can't
cant

I have a table with 7 columns:

Start_hour | Start_minute | Start_second | End_hour | End_minute | end_second | date

My task is to sum the differences between start and end times on current day. And my query already does that. Sadly I need the output to be in minutes:seconds only. For Example the total sum of time differences is 2 hour 26 minutes and 52 seconds.

I need my output to like this:

126:52

My query right now looks like this:

SELECT 
    RIGHT(CONVERT(CHAR(8), 
          DATEADD(SECOND, 
                  SUM(DATEDIFF(SECOND, 
                               Timefromparts(start_hour, start_minute, start_second, 0, 0), 
                               Timefromparts(end_hour, end_minute, end_second, 0, 0))
                 ), 0), 108), 5) 
FROM   
    opoznienia 
WHERE  
    YEAR(data) = YEAR(GETDATE()) 
    AND MONTH(data) = MONTH(GETDATE()) 
    AND DAY(data) = DAY(GETDATE()) 

Sample data:

Start_hour | Start_minute | Start_second | End_hour | End_minute | end_second | date
10  15  0   10   30   30   2018-11-27 14:40:53.680
10  15  0   10   30   30   2018-11-30 10:16:20.610
6   10  30  6    23   45   2018-12-02 01:00:27.243
8   10  0   8    53   45   2018-12-02 14:42:48.663
10  5   13  10   55   23   2018-12-02 14:53:03.560

Output of the query above:

47:13 (without RIGHT command it would be 01:47:13)

The wanted output:

107:13

You need to calculate the number of seconds and then format it as you want. From number of seconds you can calculate the minutes as division by 60, where the remainder are the seconds:

declare @NumberOfSeconds int = 7612
-- Returns 126:52
select concat(@NumberOfSeconds / 60, ':', FORMAT(@NumberOfSeconds % 60, 'D2'))

Verb Can, lift this piano by myself." The implication here is on whether you have the physical capacity or mental acuity to get the piano lifted. Can definition, to be able to; have the ability, power, or skill to: She can solve the problem easily, I'm sure. See more.

Here is one option:

SELECT
    numSeconds,
    CASE WHEN numSeconds / 60 <= 100
         THEN RIGHT('00' + CONVERT(VARCHAR(20), numSeconds / 60), 2)
         ELSE CONVERT(VARCHAR(20), numSeconds / 60) END
         + ':' +
    CASE WHEN numSeconds / 60 <= 100
         THEN RIGHT('00' + CONVERT(VARCHAR(20), numSeconds % 60), 2)
         ELSE CONVERT(VARCHAR(20), numSeconds % 60) END AS output
FROM yourTable;

Demo

The ugliness in the code has to do with that you expect a minimum of two digits for the minute and second components. So, we have to pad each component with zeroes in the case where minutes or seconds happens to be just a single digit.

May I? Can I? - English Grammar - English, : a closed metal container that is usually shaped like a cylinder and that holds food or drink. : the food or drink that is in a can. Define can. can synonyms, can pronunciation, can translation, English dictionary definition of can. to be able to, have the power or skill to: I can take a bus to the

The other answers are better, but to understand what may be missing in what you're trying to do - you can look at following where DatePart is used. -->"

..(without RIGHT command it would be 01:47:13)

" This is your clue that you that you need to work with the parts and not the whole...

SELECT Cast
(
Datepart
(
hour,
DATEADD(second,sum(datediff(second, 
TIMEFROMPARTS ( Start_hour, Start_minute, Start_second, 0, 0), 
TIMEFROMPARTS ( End_hour, End_minute, End_second, 0, 0))),0)
) * 60 
+
Datepart
(
minute,
DATEADD(second,sum(datediff(second, 
TIMEFROMPARTS ( Start_hour, Start_minute, Start_second, 0, 0), 
TIMEFROMPARTS ( End_hour, End_minute, End_second, 0, 0))),0)
) As varchar)
+ 
':'
+
Cast
(
Datepart
(
second,
DATEADD(second,sum(datediff(second, 
TIMEFROMPARTS ( Start_hour, Start_minute, Start_second, 0, 0), 
TIMEFROMPARTS ( End_hour, End_minute, End_second, 0, 0))),0)
) As varchar) as result
from minsec 

Use can in a sentence, Can was the leading avant-garde rock group of the 70s. Can experimented with noise, synthesizers, non-traditional music, cut-and-paste techniques, and, most  110 synonyms of can from the Merriam-Webster Thesaurus, plus 97 related words, definitions, and antonyms. Find another word for can. Can: to bring (as an action or operation) to an immediate end.

Try the following

CREATE TABLE T(
  StartHour INT,
  StartMinute INT,
  StartSecond INT,
  EndHour INT,
  EndMinute INT,
  EndSecond INT,
  [Date] DATE
);

INSERT INTO T VALUES
(10,  15,  0 ,  10,   30,   30, '2018-11-27'),   
(10,  15,  0 ,  10,   30,   30, '2018-11-30'),   
(6 ,  10,  30,  6 ,   23,   45, '2018-12-02'),   
(8 ,  10,  0 ,  8 ,   53,   45, '2018-12-02'),   
(10,  5 ,  13,  10,   55,   23, '2018-12-02');

SELECT *,

       CAST(  (DATEDIFF(Hour, StartTime, EndTime) * 60) +
              (DATEDIFF(Minute, StartTime, EndTime) % 60) AS VARCHAR
           ) + ':' +
       CAST(DATEDIFF(Second, StartTime, EndTime) % 60 AS VARCHAR)
FROM
(
  SELECT [Date],
         TIMEFROMPARTS(StartHour, StartMinute, StartSecond, 0, 0) StartTime,
         TIMEFROMPARTS(EndHour, EndMinute, EndSecond, 0, 0) EndTime
  FROM T
) TT

You can also SUM() and GROUP BY [Date] if you want to.

SELECT [Date],
       CAST(  SUM( (DATEDIFF(Hour, StartTime, EndTime) * 60) +
                   (DATEDIFF(Minute, StartTime, EndTime) % 60)
                 ) AS VARCHAR
           ) + ':' +
       CAST(SUM(DATEDIFF(Second, StartTime, EndTime) % 60) AS VARCHAR)
FROM
(
  SELECT [Date],
         TIMEFROMPARTS(StartHour, StartMinute, StartSecond, 0, 0) StartTime,
         TIMEFROMPARTS(EndHour, EndMinute, EndSecond, 0, 0) EndTime
  FROM T
) TT
GROUP BY [Date]

Demo


UPDATE

It seems like you are looking for

SELECT [Date],
       CAST(SUM(DATEDIFF(Second, StartTime, EndTime)) / 60 AS VARCHAR) + ':' +
       CAST(SUM(DATEDIFF(Second, StartTime, EndTime)) % 60 AS VARCHAR) [MM:SS]
FROM
(
  SELECT [Date],
         TIMEFROMPARTS(StartHour, StartMinute, StartSecond, 0, 0) StartTime,
         TIMEFROMPARTS(EndHour, EndMinute, EndSecond, 0, 0) EndTime
  FROM T
) TT
GROUP BY [Date];

Returns:

+---------------------+--------+
|        Date         | MM:SS  |
+---------------------+--------+
| 27/11/2018 00:00:00 | 15:30  |
| 30/11/2018 00:00:00 | 15:30  |
| 02/12/2018 00:00:00 | 107:10 |
+---------------------+--------+

Demo

CAN (noun) definition and synonyms, Can may refer to: Contents. 1 Containers; 2 Music; 3 Other; 4 See also. Containers[edit]. Aluminum can · Drink can · Oil can · Steel and tin cans · Trash can  A Controller Area Network (CAN bus) is a robust vehicle bus standard designed to allow microcontrollers and devices to communicate with each other's applications without a host computer. It is a message-based protocol , designed originally for multiplex electrical wiring within automobiles to save on copper, but can also be used in many other

Can, VerbEdit. can (third-person singular simple present can, present participle -, simple past could, past participle (obsolete except in adjectival use) couth). Can(stylised as CAN) was a German experimental rockband formed in Colognein 1968 by the core quartet of Holger Czukay(bass, tape editing), Irmin Schmidt(keyboards), Michael Karoli(guitar), and Jaki Liebezeit(drums).

The Official CAN / Spoon Records Website, can meaning: 1. to be able to: 2. used to say that you can and will do something: 3. to be allowed to: . Learn more. We would like to show you a description here but the site won’t allow us.

Can, 1.4Used to indicate that something is typically the case. 'antique clocks can seem out of place in modern homes'. More example sentences. Create beautiful designs with your team. Use Canva's drag-and-drop feature and layouts to design, share and print business cards, logos, presentations and more.

Comments
  • Sample data and expected results would be great. :)
  • Start by using datetime data type instead of separated fields
  • @JuanCarlosOropeza I wish I could, database is not mine and I can't modify it ;/
  • How does one minute and one second get formatted? Would it be 1:1, 01:01, or something else?
  • 01:01 @TimBiegeleisen
  • I'd use a different function to FORMAT; it is awfully slow.