1

I would like to calculate the difference between a past datetime and the current datetime with the result in [hh]:mm format.

2

Try this:

DECLARE @then datetime={ts '2014-01-23 12:34:56.789'};

SELECT LEFT(
    CONVERT(varchar(10), DATEADD(second,
        DATEDIFF(second, @then, GETDATE()),
        {d '1900-01-01'}), 8), 5);

Here's what it does:

  • Calculates the difference in seconds between @then and now (GETDATE())
  • Adds that number of seconds to 1900-01-01
  • Formats the output as hh:mm:ss
  • Returns the first five characters, i.e. hh:mm

Oh, and you can REPLACE the text "00:" with "" (nothing) if you want to hide the hour if it's less than 60 minutes:

SELECT REPLACE(LEFT(CONVERT ....
    ...., 8), 5), '00:', '')
3

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 < 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.