Calculate time difference between a datetime and the current date & time I would like to calculate the difference between a past datetime and the current datetime with the result in [hh]:mm format.
Some other approaches - I started this many hours ago and only just got back to it.

Let's first take a look at how to extract some important date part information from this data:

DECLARE @dt DATETIME = '2014-06-12T12:40:12', @d INT;
SELECT @d = DATEDIFF(MINUTE, @dt, GETDATE());

-- at the time I ran this:
SELECT 
  @d, -- 111,027 minutes
  -- or
  @d / 60, -- 1,850 hours 
  @d % 60, -- and 27 minutes
  -- or
  @d / 1440, -- 77 days
  @d % 1440, -- and 147 minutes 
  -- or
  @d / 1440,      -- 77 Days
  @d % 1440 / 60, -- and 2 hours
  @d % 60;        -- and 27 minutes


Want to discard anything more than 24 hours? Ok, just take the modulo in days (1440 minutes), we just want the remainder (2 hours, 27 minutes).

SELECT LEFT(CONVERT(TIME,DATEADD(MINUTE,@d%1440,0)),5);

-- 02:27


Want all of the hours on the left? ok:

SELECT CONVERT(VARCHAR(11), @d/60) + ':' 
  + RIGHT('0' + CONVERT(VARCHAR(11), @d%60), 2);

-- 1850:27


Need the hh: suppressed when it's &lt; 1 hour? ok:

SELECT COALESCE(NULLIF(CONVERT(VARCHAR(11), @d/60),0) + ':','') 
  + RIGHT('0' + CONVERT(VARCHAR(11), @d%60) , 2);

-- 1850:27 in this case, but just 27 if input was 27 minutes ago


-- want days broken out separately? ok:

SELECT CONVERT(VARCHAR(11), @d/1440) + ' days, '
  + RIGHT('0' + CONVERT(VARCHAR(11), (@d%1440/60)), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(11), @d%60) , 2);

-- 77 days, 02:27