Solved: How to subtract times

I am writing a report that has times in different formats, and I need to subtract them. I need the difference in times between VerifiedTime and CollectTime. Can anyone help?

I am writing the query in Visual Studio 2005 with SQL Server 2005.

Thanks! LabSample.xls
SELECT     LEFT(RIGHT(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, '03/01/1980'), 112) + SUBSTRING(CONVERT(varchar, DATEADD(ss, 
                      d.EnteredOffset + b.VerifyTime, '03/01/1980'), 108), 1, 2) + SUBSTRING(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, '03/01/1980'), 
                      108), 4, 2), 4), 4) AS VerifiedTime, DATENAME(WEEKDAY, d.CollectionDate) AS [Day of Week], d.Number, b.TestLABLSPEC, 
                      LabLTestDictionary.Orderable, d.CollectionDate, LabLTestLabSites.LabSite, b.VerifyTime, d.CollectionTime, d.ReceivedDate, d.ReceivedTime, 
                      d.CollectedBy, dbo.LabLTestDictionary.Department, RIGHT(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, '03/01/1980'), 112) 
                      + SUBSTRING(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, '03/01/1980'), 108), 1, 2) + SUBSTRING(CONVERT(varchar, 
                      DATEADD(ss, d.EnteredOffset + b.VerifyTime, '03/01/1980'), 108), 4, 2), 4) AS test, SUBSTRING(LTRIM(d.CollectionTime), 12, 8) AS CollectTime, 
                      SUBSTRING(LTRIM(d.ReceivedTime), 12, 8) AS RecdTime, SUBSTRING(LTRIM(o.OriginalCollTime), 12, 8) AS orderedTime, 
                      dbo.LabLSpecimenFilePart2.OrderLocation, LEFT(RIGHT(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, CONVERT(DATETIME, 
                      '1980-03-01 00:00:00', 102)), 112) + SUBSTRING(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, CONVERT(DATETIME, 
                      '1980-03-01 00:00:00', 102)), 108), 1, 2) + SUBSTRING(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, CONVERT(DATETIME, 
                      '1980-03-01 00:00:00', 102)), 108), 4, 2), 4), 2) AS Expr1, dbo.LabLSpecimenFilePart2.OriginalCollTime, d.Status


FROM         dbo.LabLSpecResultTests AS b INNER JOIN
                      dbo.LabLSpecimenFile AS d ON d.UrnLABLSPEC = b.UrnLABLSPEC INNER JOIN
                      dbo.LabLTestDictionary ON b.TestLABLSPEC = LabLTestDictionary.PrintNumberLABLTEST INNER JOIN
                      dbo.LabLTestLabSites ON LabLTestDictionary.PrintNumberLABLTEST = LabLTestLabSites.PrintNumberLABLTEST INNER JOIN
                      dbo.LabLSpecimenFilePart2 AS o ON o.UrnLABLSPEC = b.UrnLABLSPEC INNER JOIN
                      dbo.LabLSpecimenFilePart2 ON d.UrnLABLSPEC = dbo.LabLSpecimenFilePart2.UrnLABLSPEC
WHERE     (d.CollectionDate BETWEEN '04/01/2011' and '04/01/2011') AND (LEFT(RIGHT(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, 
                      CONVERT(DATETIME, '1980-03-01 00:00:00', 102)), 112) + SUBSTRING(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, 
                      CONVERT(DATETIME, '1980-03-01 00:00:00', 102)), 108), 1, 2) + SUBSTRING(CONVERT(varchar, DATEADD(ss, d.EnteredOffset + b.VerifyTime, 
                      CONVERT(DATETIME, '1980-03-01 00:00:00', 102)), 108), 4, 2), 4), 2) IS NOT NULL) AND (LEFT(RIGHT(CONVERT(varchar, DATEADD(ss, 
                      d.EnteredOffset + b.VerifyTime, CONVERT(DATETIME, '1980-03-01 00:00:00', 102)), 112) + SUBSTRING(CONVERT(varchar, DATEADD(ss, 
                      d.EnteredOffset + b.VerifyTime, CONVERT(DATETIME, '1980-03-01 00:00:00', 102)), 108), 1, 2) + SUBSTRING(CONVERT(varchar, DATEADD(ss, 
                      d.EnteredOffset + b.VerifyTime, CONVERT(DATETIME, '1980-03-01 00:00:00', 102)), 108), 4, 2), 4), 2) IS NOT NULL) AND 
                      (LabLTestLabSites.LabSite = 'ML') AND (dbo.LabLTestDictionary.Department <> 'pulm') AND (b.TestLABLSPEC = '100.3000') AND 
                      (dbo.LabLSpecimenFilePart2.OriginalCollTime = CONVERT(DATETIME, '1753-01-01 06:00:00', 102)) AND 
                      (dbo.LabLSpecimenFilePart2.OrderLocation in('ICU','5emed')) AND (d.Status <> 'CAN')
ORDER BY verifiedtime
Open in new window