# 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
Blog http://dynamicsgpblogster.blogspot.com/
Sunday, December 19, 2010 12:13 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

### 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
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