取数时长查询

Question

我有一台MS SQL 2005服务器。我有一个名为STAT的数据库&2个名为STARTRUN & ENDRUN的列，其中有很多行。

   STARTRUN     ENDRUN
20110910200007  20110910200017
20110910200028  20110910200037
20110910200048  20110910200057

它显示活动的开始时间和结束时间，格式为YYYYMMDDHHMMSS。此列的数据类型为VARCHAR。我正在尝试编写一个SQL脚本，以便检索每个活动的持续时间，并将其转储到csv文件中，如下所示。

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

请帮帮我。

Answer 1

首先，您必须找到一种将格式转换为datetime的方法。下面的子查询通过使其看起来像一个ODBC规范日期，然后调用convert来实现这一点。然后，您可以将更多的convert与datediff结合使用，以获得所需的输出格式。

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

这是一个用于将查询结果导出到working example at SE Data.文件的CSV请参阅this question。