<div>Wandeln Sie HH:MM:SS.MS in Sekunden um mit SQL Server 2008 R2</div>

Question

Ich habe eine Datenbank, in der die Zeit als NVARCHAR(14) gespeichert ist. Ein Beispiel dafür, was ich sehe, wenn ich die Zeit eines bestimmten Benutzers abfrage, ist 00:08:45.68.

Was ich tun möchte, ist diesen Wert von 0 Stunden, 8 Minuten, 45 Sekunden und 68 Millisekunden in einen Wert von 526 Sekunden umwandeln. Größer oder gleich 50 Millisekunden wird aufgerundet.

Ich habe auch gespeicherte Werte, bei denen keine Millisekunden erfasst wurden, z.B. 98:40:12. Es gibt viele Datensätze, in denen mehr als 24 Stunden Zeit gespeichert sind, sowohl mit Millisekunden (97:18:59.32) als auch ohne (98:40:12).

Ich sehe viele Abfragen, um Sekunden in HH:MM:SS.MS umzuwandeln, aber keine für den umgekehrten Weg. Ich sehe auch viele Abfragen, die Datumsangaben enthalten, aber ich habe kein Datum mit der Zeit gespeichert.

Hoffentlich bin ich klar genug.

Ich habe folgendes versucht:

(DATEPART(hh, trntime.time1) * 60 * 60) + (DATEPART(MI, trntime.time1) * 60) + DATEPART(s, trntime.time1) AS 'Calc_time1_Secs' 

aber ich erhalte folgende Fehlermeldung:

Msg 241, Stufe 16, Zustand 1, Zeile 4
Conversion failed when converting date and/or time from character string.

Answer 1

Jeder Stundenwert > 24 ist mit dem Typ TIME nicht wirklich nutzbar. Da alle außer dem ersten Feld eine feste Länge haben, könnten Sie folgendes tun:

;with T(f) as (select '97:18:59.32')

select 
    (LEFT(f, CHARINDEX(':', f) - 1)  * 60 * 60)
    + (SUBSTRING(f, CHARINDEX(':', f) + 1, 2) * 60)
    + SUBSTRING(f, CHARINDEX(':', f) + 4, 2)
    + CASE WHEN (SUBSTRING(f, CHARINDEX(':', f) + 7, LEN(f)) >= 50) 
             THEN 1 ELSE 0 END
from T

Oder

select 
    (LEFT(f, CHARINDEX(':', f) - 1)  * 60 * 60) 
    + CAST(ROUND(DATEDIFF(MILLISECOND, 0, '00' 
    + (SUBSTRING(f, CHARINDEX(':', f), LEN(f)))) / 1000.0, 0) AS INT)
from T

Answer 2

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE trntime
    ([time1] NVARCHAR(14))
;

INSERT INTO trntime
    ([time1])
VALUES
    ('00:08:45.68'),
    ('97:18:59.32'),
    ('97:18:59.52'),
    ('98:40:12')
;

Query 1:

SELECT (hour*60*60) + 
       (DATEPART(MI, minutes) * 60) + 
       DATEPART(S, minutes) + 
       ROUND(DATEPART(MS, minutes)/1000.0,0) AS 'Calc_time1_Secs'
FROM (SELECT LEFT(time1,2) AS 'hour', 
      '00' + RIGHT(time1,LEN(time1)-2) AS 'minutes'
      FROM trntime) AS Table1

Results:

| CALC_TIME1_SECS |
|-----------------|
|             526 |
|         350339 |
|         350340 |
|         355212 |

Query 2:

SELECT (LEFT(time1,2)*60*60) +
       ROUND(DATEDIFF(MS, 0, '00' + RIGHT(time1,LEN(time1)-2))/1000.0,0)
        AS 'Calc_time1_Secs'
FROM trntime

Results:

| CALC_TIME1_SECS |
|-----------------|
|             526 |
|         350339 |
|         350340 |
|         355212 |

Answer 3

Zuerst normalisieren Sie die Zeit, indem Sie 0 Millisekunden hinzufügen, wenn sie fehlen, ersetzen dann den Doppelpunkt durch einen Punkt und verwenden Sie parsename(), da dies immer genau 4 Teile haben wird:

declare @t table (
    Id int identity(1,1) primary key,
    TimeSec nvarchar(14) not null
);

insert into @t (TimeSec)
values
    ('00:08:45'),
    ('14:18:59.79'),
    ('97:18:59.22'),
    ('98:40:12');

select sq.Id, sq.TimeSec, sq.PreparedTime, 
    cast(parsename(sq.PreparedTime, 4) as bigint) * 3600
    + cast(parsename(sq.PreparedTime, 3) as bigint) * 60
    + cast(parsename(sq.PreparedTime, 2) as bigint)
    + case 
        when cast(parsename(sq.PreparedTime, 1) as bigint) >= 50 
            then 1 
        else 0 
      end
as [TotalSec]
from (
    select t.*, 
            replace(
                        t.TimeSec + 
                        case 
                            when charindex(N'.', t.TimeSec) = 0 
                                then '.0' 
                            else '' 
                        end,
                        ':', 
                        '.'
                    ) as [PreparedTime]
    from @t t
) sq;