Efficient Way to Convert Second to Minute and Seconds in SQL Server 2005

Efficient way to convert second to minute and seconds in sql server 2005

With hours:

SELECT CONVERT(CHAR(8),DATEADD(second,90,0),108)
00:01:30

Ignoring hours:

SELECT RIGHT(CONVERT(CHAR(8),DATEADD(second,90,0),108),5)
01:30

SQL server, Converting Seconds to Minutes, Hours, Days

I tend to use:

CAST(FLOOR(seconds / 86400) AS VARCHAR(10))+'d ' +
    CONVERT(VARCHAR(5), DATEADD(SECOND, Seconds, '19000101'), 8)

The top part just gets your days as an integer, the bottom uses SQL-Server's convert to convert a date into a varchar in the format HH:mm:ss after converting seconds into a date.

e.g.

SELECT  Formatted = CAST(FLOOR(seconds / 86400) AS VARCHAR(10))+'d ' +
                        CONVERT(VARCHAR(5), DATEADD(SECOND, Seconds, '19000101'), 8),
        Seconds
FROM    (   SELECT  TOP 10 
                    Seconds = (ROW_NUMBER() OVER (ORDER BY Object_ID) * 40000)
            FROM    sys.all_Objects
            ORDER BY Object_ID
        ) S

Example on SQL Fiddle

N.B. Change CONVERT(VARCHAR(5), DATEADD(.. to CONVERT(VARCHAR(8), DATEADD(.. to keep the seconds in the result

EDIT

If you don't want seconds and need to round to the nearest minute rather than truncate you can use:

SELECT  Formatted = CAST(FLOOR(ROUND(Seconds / 60.0, 0) * 60 / 86400) AS VARCHAR(10))+'d ' +
                        CONVERT(VARCHAR(5), DATEADD(SECOND, ROUND(Seconds / 60.0, 0) * 60, '19000101'), 8),
        Seconds
FROM    (   SELECT  Seconds = 3899
        ) S

I have just replaced each reference to the column seconds with:

ROUND(Seconds / 60.0, 0) * 60

So before doing the conversion rounding your seconds value to the nearest minute

SQL - Seconds to Day, Hour, Minute, Second

How about this:

declare @days int, 
        @dt1 smalldatetime = '2012-03-25 03:24:16', 
        @dt2 smalldatetime = getdate()

set @days = datediff (s, @dt1, @dt2)

SELECT convert(char(10), @days / (60 * 60 * 24)) + ':'
+ convert(char(10), dateadd(s, @days, convert(datetime2, '0001-01-01')), 108)

Result -- 170:20:40:00

See SQL Fiddle with Demo

Calculating timespan with t-sql

You can get the difference between the two dates to whatever resolution you want (in your example, minutes):

DATEDIFF(minute, @start_date, @end_date)

From there it's a simple matter of dividing minutes into hours and hours into days and modding the remainder.

Take the time from the datetime and convert it into seconds?

Use DATEPART:

(DATEPART(HOUR, GETDATE()) * 3600) +
(DATEPART(MINUTE, GETDATE()) * 60) +
(DATEPART(SECOND, GETDATE()))

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 */

I want Hours,Min, second difference from two datetime

this code might help you...

DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()

SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds

SQL: Is there a more efficient way to calculate elapsed hours, minutes, seconds?

In terms of finding the hours, minutes, and seconds between two dates similar to the functions you are using, you could use DatePart like so:

DatePart(hh, Finish - Start)
DatePart(mi, Finish - Start)
DatePart(s, Finish - Start)

---

Related Topics