none
How to calculate the number of years and days between two dates

    Question

  • Hi,

    I appeal to your infinite wisdom to help me with what would otherwise seem like a simple issue. I need to find a way in T-SQL to calculate the number of years and days between two dates:

    For example, between '10/31/2010' and '12/01/2012' there are a number of days, but how to say there's 1 year and some 61 days left?


    MG.-
    Mariano Gomez, MIS, MCP, PMP
    Maximum Global Business, LLC.
    http://www.maximumglobalbusiness.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Sunday, December 19, 2010 12:13 AM

Answers

  • The solution depends on your definitions of when a year has passed, and how precise the answer should be.

    The simplest solution is to calculate the number of days that have passed, and translate that back to years and days.

    Declare @start datetime
    Declare @finish datetime
    Set @start ='20101031'
    Set @finish='20121201'
    
    Declare @days int
    Set @days = DATEDIFF(day, @start, @finish)
    
    SELECT @days/365 AS Years, @days%365 AS Days
    
    Years    Days    
    ----------- ----------- 
    2      32
    
    

    Now if the definition of "one year = 365 days" is no precise enough for you, then could use an "age calculation" to determine the number of year, and based on that calculate the remaining number of days.

    Declare @start datetime
    Declare @finish datetime
    Set @start ='20101031'
    Set @finish='20121201'
    
    Declare @years int
    Set @years = (CAST(CONVERT(char(8),@finish, 112) AS int)
          - CAST(CONVERT(char(8),@start, 112) AS int))/10000
    
    Declare @days int
    Set @days = DATEDIFF(day, DATEADD(year,@years,@start), @finish)
    
    SELECT @years AS Years, @days AS Days
    
    Years    Days    
    ----------- ----------- 
    2      31
    

    Gert-Jan
    Sunday, December 19, 2010 10:30 AM
  • DECLARE

     

    @d1 DATETIME,@d2 DATETIME;

    SELECT

     

    @d1='20040229',@d2='20070227';

    SELECT

     

    (0+CONVERT(CHAR(8),@d2,112)-CONVERT(CHAR(8),@d1,112))/10000;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, December 19, 2010 11:52 AM

All replies

  • Check Calculating Age
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, December 19, 2010 12:28 AM
  • The solution depends on your definitions of when a year has passed, and how precise the answer should be.

    The simplest solution is to calculate the number of days that have passed, and translate that back to years and days.

    Declare @start datetime
    Declare @finish datetime
    Set @start ='20101031'
    Set @finish='20121201'
    
    Declare @days int
    Set @days = DATEDIFF(day, @start, @finish)
    
    SELECT @days/365 AS Years, @days%365 AS Days
    
    Years    Days    
    ----------- ----------- 
    2      32
    
    

    Now if the definition of "one year = 365 days" is no precise enough for you, then could use an "age calculation" to determine the number of year, and based on that calculate the remaining number of days.

    Declare @start datetime
    Declare @finish datetime
    Set @start ='20101031'
    Set @finish='20121201'
    
    Declare @years int
    Set @years = (CAST(CONVERT(char(8),@finish, 112) AS int)
          - CAST(CONVERT(char(8),@start, 112) AS int))/10000
    
    Declare @days int
    Set @days = DATEDIFF(day, DATEADD(year,@years,@start), @finish)
    
    SELECT @years AS Years, @days AS Days
    
    Years    Days    
    ----------- ----------- 
    2      31
    

    Gert-Jan
    Sunday, December 19, 2010 10:30 AM
  • DECLARE

     

    @d1 DATETIME,@d2 DATETIME;

    SELECT

     

    @d1='20040229',@d2='20070227';

    SELECT

     

    (0+CONVERT(CHAR(8),@d2,112)-CONVERT(CHAR(8),@d1,112))/10000;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, December 19, 2010 11:52 AM
  • Gert-Jan,

    Much thanks for taking the time to write the query. I was looking for a precise definition as I need to calculate the remaining days of depreciation for a fixed asset.

    Best regards,

    MG.-


    MG.-
    Mariano Gomez, MIS, MCP, PMP
    Maximum Global Business, LLC.
    http://www.maximumglobalbusiness.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Sunday, December 19, 2010 3:53 PM
  • Uri,

    As always, much thanks for taking the time to write the query and I do follow your blog on a regular basis.

    Best regards,


    MG.-
    Mariano Gomez, MIS, MCP, PMP
    Maximum Global Business, LLC.
    http://www.maximumglobalbusiness.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Sunday, December 19, 2010 3:54 PM
  • Noam,

    The link was helpful, but I was looking for a simplified solution.


    MG.-
    Mariano Gomez, MIS, MCP, PMP
    Maximum Global Business, LLC.
    http://www.maximumglobalbusiness.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Sunday, December 19, 2010 3:54 PM