Sql-server – Convert Varchar Duration time to time

sql server

I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is '335:55:20.97' duration time. My code is below.

      CPUTM =     335:55:20.97 duration time
     CPUZIPTM =     0:00:01.96  duration time


select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) 
       > '02:00:00.00')

Using SQL server 2012 Enterprise Edition.

Best Answer

Assuming the format in the 2 columns is hours:mm:ss[.fff] where hours can be even more than 24, I think this would work: 

SELECT smf.* 
FROM [SMF_DATA].[dbo].[System_Management_Facility] AS smf
  CROSS APPLY
    ( SELECT CHARINDEX(':', CPUTM)    AS i1, 
             CHARINDEX(':', CPUZIPTM) AS i2,
             LEN(CPUTM)    AS len1,
             LEN(CPUZIPTM) AS len2
    ) AS a
  CROSS APPLY
    ( SELECT CONVERT(int, LEFT(CPUTM, i1-1))
             + CONVERT(int, LEFT(CPUZIPTM, i2-1)) AS number_of_hours,
             CONVERT(datetime, '00:' + RIGHT(CPUTM,    len1-i1)) AS d1,  -- implicit 
             CONVERT(datetime, '00:' + RIGHT(CPUZIPTM, len2-i2)) AS d2   -- dates
                              -- both d1 and d2 get implicit date of '1900-01-01'
    ) AS b
WHERE 
    number_of_hours + DATEDIFF(hour, '19000101', d1 + d2) >= 2 ;

The CROSS APPLY are not really needed, they are there so the logic is split in parts and so you don't have a huge complicated final condition.

If you wanted a different check (say 10 minutes instead of 2 hours), it would need only this change:

WHERE 
    number_of_hours = 0
  AND
    DATEDIFF(minute, '19000101', d1 + d2) >= 10 ;
Related Question