How to calculate age in T-SQL with years, months, and days

by

Here is some T-SQL that gives you the number of years, months, and days since the day specified in @date. It takes into account the fact that DATEDIFF() computes the difference without considering what month or day it is (so the month diff between 8/31 and 9/1 is 1 month) and handles that with a case statement that decrements the result where appropriate.

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date="2/29/04"

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

More Related Contents:

  1. SQl CASE Statement
  2. Levenshtein distance in T-SQL
  3. How to get sp_executesql result into a variable?
  4. How can I use optional parameters in a T-SQL stored procedure?
  5. Select query to remove non-numeric characters
  6. T-SQL Decimal Division Accuracy
  7. Selecting a Record With MAX Value
  8. T-SQL Pivot? Possibility of creating table columns from row values
  9. What is a “batch”, and why is GO used?
  10. TSQL divide by zero encountered despite no columns containing 0
  11. T-SQL loop over query results
  12. How to insert default values in SQL table?
  13. How to Round a Time in T-SQL
  14. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  15. Return a value if no rows are found in Microsoft tSQL
  16. Check if a varchar is a number (TSQL)
  17. How to pivot table with T-SQL?
  18. Using Aliases in Where Clause or an Alternative Option?
  19. Extract the first word of a string in a SQL Server query
  20. How to convert number of minutes to hh:mm format in TSQL?
  21. Testing for inequality in T-SQL
  22. Can I use the MERGE statement in SQL Server 2005?
  23. Replace a newline in TSQL
  24. Search for one value in any column of any table inside a database
  25. How to use an Alias in a Calculation for Another Field
  26. Check if a varchar is a number (T-SQL)
  27. Using a cursor with dynamic SQL in a stored procedure
  28. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  29. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  30. How to parse string into date?

Leave a Comment