Retrieve duration query

Question

I have a MS SQL 2005 server. I have a database by name STAT & 2 columns by name STARTRUN & ENDRUN and have many rows in it.

   STARTRUN     ENDRUN
20110910200007  20110910200017
20110910200028  20110910200037
20110910200048  20110910200057

It shows the start time and end time of an activity and is in YYYYMMDDHHMMSS format. The datatype for this column is VARCHAR. I am trying to write a SQL script where i can retrieve the duration of each activity and dump it to a csv file as shown below.

START DATE  START TIME      END DATE    END TIME    DURATION
10-09-2011  8:00:07 PM      11-09-2011  1:10:10 AM      5:10:03

Please help me.

What have you tried? What didn't work? Where exactly are you having difficulties? — Oded, Sep 11 '11 at 07:52
Ignoring the fact that this should be datetime, why then use varchar for fixed length character data? — gbn, Sep 11 '11 at 09:01
Agreed gbn. Datetime or at least bigint (8 bytes either way). Datetime would make the solution much less complicated. — brian, Sep 12 '11 at 02:01

score 1 · Answer 1 · edited Jan 18 '21 at 12:32

First you'd have to find a way to convert your format to a datetime. The subquery below does that by making it look like an ODBC canonical date and then calling convert. Then you can combine more convert with datediff to get your desired output format.

select  convert(varchar, startrun, 105) + ' ' + 
        substring(convert(varchar, startrun, 109), 13, 8) + ' ' + 
        substring(convert(varchar, startrun, 109), 25, 2)
,       convert(varchar, endrun, 105) + ' ' + 
        substring(convert(varchar, endrun, 109), 13, 8) + ' ' + 
        substring(convert(varchar, endrun, 109), 25, 2)
,       substring('0' + cast(datediff(hh, startrun, endrun) 
            as varchar), 1, 2) + ':' +
        substring('0' + cast(datediff(mi, startrun, endrun) % 60 
            as varchar), 1, 2) + ':' +
        substring('0' + cast(datediff(s, startrun, endrun) % 60*60 
            as varchar), 1, 2)
from    (
        select  convert(datetime,
                substring(startrun,1,4) + '-' +
                substring(startrun,5,2) + '-' +
                substring(startrun,7,2) + ' ' +
                substring(startrun,9,2) + ':' +
                substring(startrun,11,2) + ':' +
                substring(startrun,13,2),
                120) as startrun
        ,       convert(datetime,
                substring(endrun,1,4) + '-' +
                substring(endrun,5,2) + '-' +
                substring(endrun,7,2) + ' ' +
                substring(endrun,9,2) + ':' +
                substring(endrun,11,2) + ':' +
                substring(endrun,13,2),
                120) as endrun
        from    @YourTable
        ) as SubQueryAlias

Here's a working example at SE Data. See this question for exporting the result of a query to a CSV file.

Retrieve duration query

1 Answers1