Finding age in dd:hh:mm:ss:ms format in SQL Server

Question

I have a SQL Server instance and with a date table with datetime values. I have been trying to write queries to find the age of those columns by subtracting the current time value from the column values using GETDATE().

I have tried queries like the following:

but I'm getting errors like these:

Any help would he appreciated! Thanks!

An image of your query? And the error message. Why? Please replace both images with text. — Paul Maxwell, Apr 07 '23 at 13:58

score 0 · Answer 1 · answered Apr 07 '23 at 14:08

Copied from Date difference formatted in Days Hours Minutes Seconds

DECLARE @date DATETIME = '2023/01/01 12:29:51'
      , @x INT
  
SET @x = DATEDIFF(S, @Date, GETDATE());

SELECT @date TestDate
     , CONVERT(VARCHAR(10), ( @x / 86400 )) + ' Days '
       + CONVERT(VARCHAR(10), ( ( @x % 86400 ) / 3600 )) + ' Hours '
       + CONVERT(VARCHAR(10), ( ( ( @x % 86400 ) % 3600 ) / 60 ))
       + ' Minutes ' + CONVERT(VARCHAR(10), ( ( ( @x % 86400 ) % 3600 ) % 60 ))
       + ' Seconds';

score 0 · Answer 2 · answered Apr 07 '23 at 14:15

Try this:

DECLARE @FirstDate  DATETIME2(3) = '2021-01-01 00:00:00.000'
       ,@SecondDate DATETIME2(3) = '2022-01-01 23:59:59.999';

DECLARE @BuffDate DATETIME2(3) = '2001-01-01 00:00:00.000';

SELECT CONCAT
       (
            DATEDIFF_BIG(MILLISECOND, @FirstDate, @SecondDate) / 86400000 
            ,' '
            ,CONVERT(VARCHAR, DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND, @FirstDate, @SecondDate) % 86400000, @BuffDate), 114)
       );

The idea is get the diff in milliseconds, the extract the day from the diff using / 86400000 . The rest part % 86400000 will be the milliseconds which are smaller then one day and can be converted to HH:MM:SS.MS.

Of course, you can play with the milliseconds precision.

Finding age in dd:hh:mm:ss:ms format in SQL Server

2 Answers2