Collections:
Calculating Age in Days, Hours and Minutes in SQL Server
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL?
✍: FYIcenter.com
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 ----------->|
⇒ Getting Month and Weekday Names from DATATIME Values in SQL Server
⇐ DATEDIFF() - Calculating DATETIME Value Differences in SQL Server
⇑ Date/Time Operations and Functions in SQL Server Transact-SQL
2017-02-14, 9727🔥, 0💬
Popular Posts:
Where to find tutorials to answer some frequently asked questions on Microsoft SQL Server Transact-S...
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...