Home >> FAQs/Tutorials >> SQL Server FAQ

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

  1. How To Add or Remove Days on Date and Time Values?
  2. Can Date and Time Values Be Converted into Integers?
  3. Can Integers Be Converted into Date and Time Values?
  4. Are DATETIME and NUMERIC Values Convertible?
  5. Can a DATETIME Value Be Subtracted from Another DATETIME Value?
  6. What Are the Date and Time Functions Supported by SQL Server 2005?
  7. How To Increment or Decrement Parts of DATETIME Values?
  8. How To Use DATEADD() Function?
  9. How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?
  10. How To Calculate Age in Days, Hours and Minutes?
  11. How To Get Month and Weekday Names from DATETIME Values?
  12. How To Get Parts of DATETIME Values as Integers?
  13. How To Get Year, Month and Day Out of DATETIME Values?
  14. What Is the Difference Between GETDATE() and GETUTCDATE()?
  15. How To Format Time Zone in +/-hh:mm Format?
  16. How To Format DATETIME Values to Strings with the CONVERT() Function?
  17. How To Truncate DATETIME Values to Dates without Time?
  18. How To Set Different Parts of a DATETIME Value?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...