How to convert second into datetime's 108 format hh:mm:ss in sql server without writing function

Question

    select v1.*, datediff(ss,v1.dateofchange,v2.dateofchange) as acutaltime 
from vActualTime v1 left join vActualTime v2
    on v1.rowno=v2.rowno-1 

FK_PatientId FK_Status_PatientId DateofChange            rowno                acutaltime
------------ ------------------- ----------------------- -------------------- -----------
3            16                  2010-08-02 15:43:46.000 1                    757
3            24                  2010-08-02 15:56:23.000 2                    96
3            26                  2010-08-02 15:57:59.000 3                    NULL

I am using Sql server 2005

When I writes this

    select v1.*, datediff(mi,v1.dateofchange,v2.dateofchange) as acutaltime,
    convert(datetime,datediff(mi,v1.dateofchange,v2.dateofchange),108) as [date] 
from vActualTime v1 left join vActualTime v2
    on v1.rowno=v2.rowno-1 

I gets this

FK_PatientId FK_Status_PatientId DateofChange            rowno                acutaltime  date
------------ ------------------- ----------------------- -------------------- ----------- -----------------------
3            16                  2010-08-02 15:43:46.000 1                    13          1900-01-14 00:00:00.000
3            24                  2010-08-02 15:56:23.000 2                    1           1900-01-02 00:00:00.000
3            26                  2010-08-02 15:57:59.000 3                    NULL        NULL

This should have been given as 00-00-000 00:13:00:0000

Answer 1

Short answer: You can't.

Long answer:

First, the calculated value in minutes is interpreted as days in the CONVERT function. You'd need to divide by (24 * 60) to actually add minutes.

DATETIME only covers the years 1753 to 9999 (MSDN). Note the statement

Microsoft® SQL Server™ rejects all values it cannot recognize as dates between 1753 and 9999.

Even if you use DATETIME2 (requiring SQL Server 2008?), your date intervall only starts with the year 1. (MSDN)

Sql2008 adds a new data type called TIME, which might solve your problem.

Answer 2

From what I understand you need to take your calculated minutes (the datediff you're doing) and display that in a time format 108.

This should convert the minutes to a datetime format of 108, i.e. hh:mm:ss

select convert(varchar
              ,dateadd(minute
                      , datediff(mi,v1.dateofchange,v2.dateofchange), '00:00:00')
              , 108
              )