Comment calculer la différence entre deux champs datetime (aaaa-mm-jj hh: mm: ss) dans le format de l'heure (hh: mm: ss) dans le server sql?

J'ai 2 colonnes dans ma table dans le server sql – [Temps d'Occurrence (NT)] et [Temps de Dégagement (NT)] ayant le format aaaa-mm-jj hh: mm: ss et une troisième colonne [Durée d'interruption] avec le format hh: mm: ss

[Identifiant] | [Temps d'apparition (NT)] | [Temps de dédouanement (NT)] | [Durée de la panne]

4 | 2014-12-28 15: 06: 33.000 | 2014-12-28 15: 18: 18.000 | 00: 11: 45.000

La durée d'interruption est calculée comme la différence de [Temps d'occurrence (NT)] et [Temps de libération (NT)] Actuellement, mon code pour le faire est le suivant:

select distinct a.[Identifier] ,a.[Occurrence Time(NT)] ,a.[Clearance Time(NT)] ,cast((cast(a.[Clearance Time(NT)] as datetime) - cast(a.[Occurrence Time(NT)]as datetime)) as time ) as [Outage Duration] from final_report_2 a 

La dissortingbution est juste une security car les deux colonnes sont déjà datetime. Ce code fonctionne pour tous les cas où [Temps d'apparition (NT)] et [Temps de libération (NT)] tombent le même jour, c'est-à-dire que la panne est dans les 24 heures

Par exemple dans la rangée ci-dessus no. 4 ont tous les deux le 28ème jour et ainsi la panne est calculée correctement.

Mais la durée de la panne est calculée à tort en cas de jours différents.

2678 | 2014-12-28 12: 50: 04.000 | 2014-12-31 23: 59: 59.000 | 11: 09: 55.000

Au rang 2678, l'heure devrait être 83:09:55 au lieu de 11:09:55.

J'ai donc besoin de prendre en count la différence des jours, puis de calculer la [Durée de l'interruption] au format hh: mm: ss.

L'un des moyens possibles de le faire est le suivant:

(23:59:59 – 12:50:04) + 00:00:01 + ((31-28-1) * 24): 00: 00 + 23:59:59

où la première partie calcule l'heure du premier jour, alors la seconde partie calcule le non. de jours entiers entre le [Temps Occurrence (NT)] et le [Temps de Liquidation (NT)] et le multiplie par 24 et la partie finale représente l'heure du dernier jour.

Comment puis-je implémenter le ci-dessus dans le server sql? Peut-il être fait en utilisant les fonctions DATEPART ou DATEADD?

vous devez utiliser la fonction datediff .

Trouvez d'abord la difference en seconds entre les deux dates.

 select Datediff(SECOND, [Clearance Time(NT)], [Occurrence Time(NT)]) 

Maintenant, convertissez les seconds en hh:mm:ss utilisant ce code.

 DECLARE @TimeinSecond INT SET @TimeinSecond = 86399 -- Change the seconds SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' + RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2) + ':' + RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2) 

Changez votre requête quelque chose comme ça.

 SELECT DISTINCT a.[Identifier], a.[Occurrence Time(NT)], a.[Clearance Time(NT)], RIGHT('0' + Cast(Datediff(SECOND, [Clearance Time(NT)],[Occurrence Time(NT)]) / 3600 AS VARCHAR), 2) + ':' + RIGHT('0' + Cast((Datediff(SECOND, [Clearance Time(NT)],[Occurrence Time(NT)]) / 60) % 60 AS VARCHAR), 2) + ':' + RIGHT('0' + Cast(Datediff(SECOND, [Clearance Time(NT)],[Occurrence Time(NT)]) % 60 AS VARCHAR), 2) [Outage Duration] FROM final_report_2 a 

Référence: conversion des seconds en hh:mm:ss partir de ce lien

Bon vieux SQL DATEDIFF. Voici la fonction de durée que j'utilise. Tout enveloppé dans un UDF.

 CREATE FUNCTION fDuration(@DtStart DATETIME, @DtEnd DATETIME) RETURNS VARCHAR(10) AS BEGIN RETURN ( SELECT CONVERT(VARCHAR(10), t.Hours) + ':' + RIGHT('00' + CONVERT(VARCHAR(10), t.Minutes), 2) + ':' + RIGHT('00' + CONVERT(VARCHAR(10), t.Seconds - (t.Hours * 60 * 60) - (t.Minutes) * 60), 2) as Value FROM ( SELECT t.Hours, ABS(t.Seconds / 60 - (t.Hours * 60)) as Minutes, t.Seconds FROM ( SELECT DATEDIFF(SECOND, @DtStart, @DtEnd) as Seconds, DATEDIFF(SECOND, @DtStart, @DtEnd)/60/60 AS Hours) t ) t) END 

USAGE:

 SELECT dbo.fDuration('2014-12-28 12:50:04.000', '2014-12-31 23:59:59.000') 

RÉSULTATS :

 83:09:55 

Dans votre cas, vous pouvez modifier votre requête comme ceci

 SELECT DISTINCT a.[Identifier], a.[Occurrence Time(NT)], a.[Clearance Time(NT)], dbo.fDuration(a.[Occurrence Time(NT)], a.[Clearance Time(NT)]) as [Outage Duration] FROM final_report_2 a 

Ou même append [Durée d'interruption] en tant que champ calculé dans final_report_2 avec la formule

 dbo.fDuration([Occurrence Time(NT)],[Clearance Time(NT)]) 

Ensuite, il devient un simple choix …

 SELECT DISTINCT a.[Identifier], a.[Occurrence Time(NT)], a.[Clearance Time(NT)], a.[Outage Duration] FROM final_report_2 a 

Remarque Les heures ne sont pas préfixées avec un '0'. Cela permet des durées négatives.

J'espère que cela pourra aider