SELECT
OPENDATE, ASSNDATE,
CAST(minutes_diff / 60 AS varchar(3)) + ':' +
RIGHT('0' + CAST(minutes_diff % 60 AS varchar(2)), 2) AS Date_Diff
FROM (
SELECT GETDATE() AS OPENDATE, DATEADD(MINUTE, -9, GETDATE()) AS ASSNDATE UNION ALL -- lt 60 mins
SELECT GETDATE(), DATEADD(MINUTE, -196, GETDATE()) UNION ALL -- gt 60 mins, lt 24 hrs
SELECT GETDATE(), DATEADD(MINUTE, -(24*60+112), GETDATE()) -- gt 24 hrs
) AS test_data
CROSS APPLY (
SELECT DATEDIFF(MINUTE, ASSNDATE, OPENDATE) AS minutes_diff
) AS assign_alias_names_to_calculated_values_1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.