Calculating Age in Days, Hours and Minutes in SQL Server

Q

How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL?

✍: FYIcenter.com

A On many Web sites, news items or blog articles are displayed with a publishing data and time represented in days, hours and minutes. To do this you can use the DATEDIFF() function with a simple algorithm as shown in the tutorial exercise below:

```-- Age in days
DECLARE @submit_date DATETIME;
SET @submit_date = '2007-05-11 16:10:41.403';
SELECT 'Posted '
+ CONVERT(VARCHAR(40),
DATEDIFF(day, @submit_date, GETDATE()))
+ ' days ago.';
GO
Posted 8 days ago.

-- Age in days, hours and minutes - Wrong
DECLARE @submit_date DATETIME;
SET @submit_date = '2007-05-11 16:10:41.403';
SELECT 'Posted '
+ CONVERT(VARCHAR(40),
DATEDIFF(day, @submit_date, GETDATE()))
+ ' days, '
+ CONVERT(VARCHAR(40),
DATEDIFF(hour, @submit_date, GETDATE()))
+ ' hours, and '
+ CONVERT(VARCHAR(40),
DATEDIFF(minute, @submit_date, GETDATE()))
+ ' minutes ago.';
GO
Posted 7 days, 186 hours, and 11202 minutes ago.

-- Age in days, hours and minutes - Correct
DECLARE @submit_date DATETIME;
SET @submit_date = '2007-05-11 16:10:41.403';
SELECT 'Posted '
+ CONVERT(VARCHAR(40),
DATEDIFF(minute, @submit_date, GETDATE())/(24*60))
+ ' days, '
+ CONVERT(VARCHAR(40),
DATEDIFF(minute, @submit_date, GETDATE())%(24*60)/60)
+ ' hours, and '
+ CONVERT(VARCHAR(40),
DATEDIFF(minute, @submit_date, GETDATE())%60)
+ ' minutes ago.';
GO
Posted 7 days, 18 hours, and 42 minutes ago.
```

Notice how the correct number of days, hours, and minutes are calculated using the concepts of whole day, remaining partial day, whole hour, and remaining partial hour as shown below:

```|...................|...................|...+...+..

|<------- number of whole days -------->|<------->|
remaining partial day ---^

|<----------- number of whole hours ----------->|-|
remaining partial hour ---^

|<----------- total number of minutes ----------->|
```
• Number of whole days: DATEDIFF(minute, @submit_date, GETDATE())/(24*60), where 24*60 is the number of minutes in a whole day.
• Number of minutes in the remaining partial day: DATEDIFF(minute, @submit_date, GETDATE())%(24*60).
• Number of minutes in the remaining partial hour: DATEDIFF(minute, @submit_date, GETDATE())%60.

2017-02-14, 1614👍, 0💬