How to convert seconds to day time

Question

Example:

seconds = 272920

My Expected output = 3Days hours:minutes:seconds

I am trying to convert the seconds to day time.

Right now I am currently converted it to time using these query:

SELECT CONVERT(SEC_TO_TIME(Seconds), CHAR) FROM table;

and my output is 75:48:40 -------> Format(hh:mm:ss)

But my expected value is days, hours, mins, secs

.

Any suggestion is a big help, thanks in advance..

score 2 · Accepted Answer · answered Sep 08 '15 at 06:14

2

Try;

SELECT 
CONCAT(
    FLOOR(HOUR(SEC_TO_TIME(272920)) / 24), ' days ',
    MOD(HOUR(SEC_TO_TIME(272920)), 24), 'H:',
    MINUTE(SEC_TO_TIME(272920)), 'Min:',
    second(SEC_TO_TIME(272920)), 'Sec'
)

answered Sep 08 '15 at 06:14

Praveen

8,945
4
31
49

score 1 · Answer 2 · edited May 23 '17 at 12:29

1

DECLARE @s INT = 139905;

SELECT 
    CONVERT(VARCHAR(12), @s /60/60/24)   + ' Day(s), ' 
  + CONVERT(VARCHAR(12), @s /60/60 % 24) + ' Hour(s), '
  + CONVERT(VARCHAR(2),  @s /60 % 60)    + ' Minute(s), ' 
  + CONVERT(VARCHAR(2),  @s % 60)        + ' Second(s).';

For more details look at this below link SQL Server Convert seconds to Days & hours

edited May 23 '17 at 12:29

Community

1
1

answered Sep 08 '15 at 06:04

Dilmah

1,137
11
14

1

The question is tagged as My SQL and not SQL Server – Rahul Tripathi Sep 08 '15 at 06:04

Robby Cornelissen · Answer 3 · 2015-09-08T06:38:35.140

With some basic arithmetic, using a combination of modulo and integer division operators:

SELECT CONCAT(
    seconds DIV (60 * 60 * 24),
    ' Days, ',
    seconds DIV (60 * 60) % 24,
    ':',
    seconds DIV 60 % 60,
    ':',
    seconds % 60)　FROM table;

With your example input value of 272920:

SELECT CONCAT(
    272920 DIV (60 * 60 * 24),
    ' Days, ',
    272920 DIV (60 * 60) % 24,
    ':',
    272920 DIV 60 % 60,
    ':',
    272920 % 60);

This will yield the following output:

3 Days, 3:48:40

How to convert seconds to day time

3 Answers3