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

Try this:

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

SELECT LEFT(
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:', '')
``````

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