SQL Server FAQ - Calculating Age in Days, Hours and Minutes By: FYIcenter.com (Continued from previous topic...) How To Calculate Age in Days, Hours and Minutes? 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. (Continued on next topic...)
 Related Articles: More... Other Tutorials/FAQs: More... Related Resources: More... Selected Jobs: More...