[SQL] MM : HH에 DATEDIFF SS 형식

MM : HH에 DATEDIFF SS 형식

나는 시작 시간과 종료 시간에 일부 데이터 제공, 시간, 분, 초, 평균 길이의 측면에서 전체 길이를 계산해야합니다.

예를 들어 그 결과는 45시간 15 분 10 초, 30 분 07 초 동안 30:07 수단 45:15:10 같은 것을해야한다.

우리는 SQL Server 2008 R2를 사용하고 있고 시간 이상 24:59:59 때 변환에 실패했습니다. 나는이 작업을 수행 할 수있는 방법의 어떤 생각?

자세한 내용은 테이블의 열 등 내가 녹음이 달의 계산이 포함 된 월별 보고서, 이러한 기록의 총 길이, 평균 길이를 만들 필요가 아이디, StartDateTime, EndDateTime이다. 나는이 모든 것을 수행 할 수있는 쉬운 방법이 있는지 알고 싶습니다.

해결법

1. ==============================

   1.당신은 시간을 변환하지 말아야 -이, 하나의 24 시간 시계 시간에 포인트를 저장하지 않는 기간 또는 의미 간격 (심지어 <자체에 명확하게 데이터가 아닌 24 시간, 제한 하나) . 또한 응용 프로그램에 초를 반환하는 것이 바람직 수 있습니다 (대신에 당신이 (당신의 경우 초) 필요한 최소 간격에 DATEDIFF 걸릴 수 있습니다, 그리고 당신이 필요로하는 출력 형식으로 제시하는 몇 가지 수학, 문자열 조작을 수행하거나 도구를보고)가이 작업을 할 수 있습니다.

   당신은 시간을 변환하지 말아야 -이, 하나의 24 시간 시계 시간에 포인트를 저장하지 않는 기간 또는 의미 간격 (심지어 <자체에 명확하게 데이터가 아닌 24 시간, 제한 하나) . 또한 응용 프로그램에 초를 반환하는 것이 바람직 수 있습니다 (대신에 당신이 (당신의 경우 초) 필요한 최소 간격에 DATEDIFF 걸릴 수 있습니다, 그리고 당신이 필요로하는 출력 형식으로 제시하는 몇 가지 수학, 문자열 조작을 수행하거나 도구를보고)가이 작업을 할 수 있습니다.

   DECLARE @d TABLE
   (
     id INT IDENTITY(1,1), 
     StartDateTime DATETIME, 
     EndDateTime DATETIME
   );
   
   INSERT @d(StartDateTime, EndDateTime) VALUES 
   (DATEADD(DAY, -2, GETDATE()), DATEADD(MINUTE, 15, GETDATE())),
   (GETDATE()                  , DATEADD(MINUTE, 22, GETDATE())),
   (DATEADD(DAY, -1, GETDATE()), DATEADD(MINUTE,  5, GETDATE())),
   (DATEADD(DAY, -4, GETDATE()), DATEADD(SECOND, 14, GETDATE()));
   
   ;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
     d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
     a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
     FROM @d
   )
   SELECT id, StartDateTime, EndDateTime,
     [delta_HH:MM:SS] = CONVERT(VARCHAR(5), d/60/60)
     + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
     + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
     [avg_HH:MM:SS] = CONVERT(VARCHAR(5), a/60/60)
     + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
     + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
   FROM x;
   

   결과 :

   id  StartDateTime        EndDateTime          delta_HH:MM:SS  avg_HH:MM:SS
   --  -------------------  -------------------  --------------  ------------
   1   2013-01-19 14:24:46  2013-01-21 14:39:46  48:15:00        42:10:33
   2   2013-01-21 14:24:46  2013-01-21 14:46:46   0:22:00        42:10:33
   3   2013-01-20 14:24:46  2013-01-21 14:29:46  24:05:00        42:10:33
   4   2013-01-17 14:24:46  2013-01-21 14:25:00  96:00:14        42:10:33
   

   델타 <1 시간 동안 SS : 이것은 단지 MM 표시되지 않습니다 당신이, 요청 정확하게 무엇을하지 않습니다. 당신은 간단한 CASE 표현식이를 조정할 수 있습니다 :

   ;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
     d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
     a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
     FROM @d
   )
   SELECT id, StartDateTime, EndDateTime,
     [delta_HH:MM:SS] = CASE WHEN d >= 3600 THEN 
       CONVERT(VARCHAR(5), d/60/60) + ':' ELSE '' END
     + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
     + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
     [avg_HH:MM:SS] = CASE WHEN a >= 3600 THEN 
       CONVERT(VARCHAR(5), a/60/60) + ':' ELSE '' END
     + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
     + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
   FROM x;
   

   이 쿼리 0시 22분 0초 22:00로 상기 결과의 둘째 행 델타 열을 변경한다.

2. ==============================

   2.차이가 너무 큰 경우이 오류와 함께 종료 될 수 있습니다 나는 약간 Avinash의 답변을 수정했습니다. mm : 만 HH해야하는 경우 SS 그것을 초이 같은 ONY 수준에서 구별하기에 충분하다 :

   차이가 너무 큰 경우이 오류와 함께 종료 될 수 있습니다 나는 약간 Avinash의 답변을 수정했습니다. mm : 만 HH해야하는 경우 SS 그것을 초이 같은 ONY 수준에서 구별하기에 충분하다 :

   SELECT CONVERT(time, 
     DATEADD(s, 
       DATEDIFF(s, 
         '2018-01-07 09:53:00', 
         '2018-01-07 11:53:01'), 
        CAST('1900-01-01 00:00:00.0000000' as datetime2)
      )
   )
   

3. ==============================

   3.

   SELECT CONVERT(time, 
                  DATEADD(mcs, 
                          DATEDIFF(mcs, 
                                   '2007-05-07 09:53:00.0273335', 
                                   '2007-05-07 09:53:01.0376635'), 
                          CAST('1900-01-01 00:00:00.0000000' as datetime2)
                         )
                 )
   

4. ==============================

   4.당신이 평균을 수행 할 경우, 가장 좋은 방법은 초 하루의 분수로 변환하는 것입니다. 같은 당신이 일을 할 수 있기 때문에 일 분획, SQL 서버에 편리합니다 :

   당신이 평균을 수행 할 경우, 가장 좋은 방법은 초 하루의 분수로 변환하는 것입니다. 같은 당신이 일을 할 수 있기 때문에 일 분획, SQL 서버에 편리합니다 :

   select avg(cast(endtime - starttime) as float)
   from t
   

   당신은 역 캐스트를 사용하여 날짜로 다시 변환 할 수 있습니다 :

   select cast(avg(cast(endtime - starttime as float) as datetime)
   from t
   

   산술 원하는 형식으로 시간을 얻을 수 있습니다. . . 그게 고통이다. 당신은 최종 형식으로 일을 포함하고, 사용을 고려할 수 있습니다

   select right(convert(varchar(255), <val>, 120), 10)
   

   24을 초과하는 시간을 얻으려면, 여기에 또 다른 방법은 다음과 같습니다

   select cast(floor(cast(<val> as float)*24) as varchar(255))+right(convert(varchar(255), <val>, 120), 6)
   

   그것은 왼쪽에 0으로 채워해야한다 분, 초, 대한 변환을 사용합니다. 그런 다음 별도의 값으로 시간을 추가한다.

5. ==============================

   5.SQL 서버 2012 년부터는 사용 DATEDIFF 함수에 필요하지 않습니다. 당신은 당신이 원하는 것을 달성하기 위해 FORMAT 기능을 사용할 수 있습니다 :

   SQL 서버 2012 년부터는 사용 DATEDIFF 함수에 필요하지 않습니다. 당신은 당신이 원하는 것을 달성하기 위해 FORMAT 기능을 사용할 수 있습니다 :

   SELECT
       FORMAT(CONVERT(TIME, [appoitment].[Start] - [appointment].[End]), N'hh\:mm') AS 'Duration'
   FROM
       [tblAppointment] (NOLOCK)
   

6. ==============================

   6.을 피가 넘쳐 일을 포함하고 출력 밀리 초에 모든 길을 갈 수있는 방법 :

   을 피가 넘쳐 일을 포함하고 출력 밀리 초에 모든 길을 갈 수있는 방법 :

   DECLARE @startDate AS DATETIME = '2018-06-01 14:20:02.100'
   DECLARE @endDate AS DATETIME = '2018-06-02 15:23:09.000'
   SELECT CAST(DATEDIFF(day,'1900-01-01', @endDate - @startDate) AS VARCHAR) +  'd ' + CONVERT(varchar(22), @endDate - @startDate, 114)
   

   위의 의지 반환

   그리고 물론 떨어져, 당신은 당신의 선택의 서식을 사용할 수 있습니다

   DATEDIFF 각 "날짜 부분의 경계에 대한 ONE 계산하기 때문에 SQL은 MIN 일에 새로운 날짜 상대를 출력 날짜 빼기가 DATEDIFF보다 더이 작품을 (예 : 1900-01-01 들어, 당신은 아마 일부 시스템 변수에서이 값을 얻을 수 있습니다) 지원 "교차, 경과 시간이 덜 전체 datapart보다 경우에도 마찬가지입니다. 이 방법에 대한 또 다른 좋은 점은 당신이 변환 포맷 날짜를 사용할 수 있다는 것입니다.

from https://stackoverflow.com/questions/14445600/datediff-in-hhmmss-format by cc-by-sa and MIT license