How to Convert an Integer (Time) to Hh:Mm:Ss::00 in SQL Server 2008

How to convert Seconds to HH:MM:SS using T-SQL

You want to multiply out to milliseconds as the fractional part is discarded.

SELECT DATEADD(ms, 121.25 * 1000, 0)

If you want it without the date portion you can use CONVERT, with style 114

SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)

Integer into HH.MM in ssrs

I would question why you are wanting to present your data in this way and suggest that you consider a different approach, such as adding the seconds to a start time, depending on your data and reporting requirements.

In short, try your best to avoid text formatting within SQL. It is not built to do this.

---

That aside, to answer your question; this is fairly standard integer division and modulo based maths. You want to firstly convert your seconds into minutes by dividing by 60 and then into hours by dividing by 60 again. A modulo can be used instead to get the minutes remaining from the hour calculation. The rest is text formatting:

declare @t table (s int);
insert into @t values(3600),(28800)

select s
        ,right('00' + cast(s/60/60 as varchar(2)),2) + '.' + right('00' + cast(s/60%60 as varchar(2)),2) as Formatted
from @t

Output:

+---+-------+-----------+
|   |   s   | Formatted |
+---+-------+-----------+
| 1 |  3600 | 01:00     |
| 2 | 28800 | 08:00     |
+---+-------+-----------+

How to convert number of minutes to hh:mm format in TSQL?

You can convert the duration to a date and then format it:

DECLARE
    @FirstDate datetime,
    @LastDate datetime

SELECT
    @FirstDate = '2000-01-01 09:00:00',
    @LastDate = '2000-01-01 11:30:00'

SELECT CONVERT(varchar(12), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30:00:000 */

For less precision, modify the size of the varchar:

SELECT CONVERT(varchar(5), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30 */

SQL Can't convert to time format computed ss to HH:MM:SS

If I was going to turn 263827 into hours minutes and seconds, I’d use dateadd

SELECT FORMAT(DATEADD(SECOND, 263827, '1970-01-01'), 'HH:mm:ss')

Pre 2012 versions of sqlserver don’t have format:

SELECT CONVERT(VARCHAR, DATEADD(SECOND, 263827, '1970-01-01'), 8)

Theres no particular reason I chose 1st jan 1970, any date with a time of midnight would work, but this date is the unix epoch so as a technique it can be used for turning unix time (INT64 number of seconds since jan 1, 1970) into a usable DATETIME..

Note that this approach doesn’t work for seconds over 86400 because it is limited to 24 hours before it rolls over to the next day. You could FORMAT to add "day of year" if you wanted e.g. up to "365d 23:59:59" of duration.

To turn a number of seconds into hundreds of hours, do it mathematically:

SELECT CONCAT(263827/3600, FORMAT(DATEADD(SECOND, 263827, '1970-01-01'), ':mm:ss'))

This is the simplest (shortest code) way I can think of without getting into lots of divide and mod operations (messy)

How to convert hh:mm:ss to seconds in SQL Server with more than 24 hours

Try splitting each time into its component parts by converting the time to a string and then multiplying by the number of seconds relevant to each part.

Data conversion to integer is implicit

select Sum(Left(WorkHrs,2) * 3600 + substring(WorkHrs, 4,2) * 60 + substring(WorkHrs, 7,2))
from tblAttend

---

Related Topics