How do I convert a timespan into years, months and days in sql

I am getting the difference between 2 dates using the following syntax:

DATEDIFF(minute, [Dam_Date_Of_Birth], [Calf_Date_Of_Birth]) as [minutes]

This query gives me the following result:

minutes
1748160
3711420
768960
4231380
5816779

How can I get these values into a year, month, day format?

Answers


You can do it like this as well

DECLARE @diff float = 1748160 / 24.0 / 60.0
Declare @date datetime = convert(datetime, @diff);
Select DatePart(yy, @date) - 1900 as Years, DatePart(mm, @date) - 1 Months, DatePart(dd, @diff) - 1 Days

or you can simply add your minutes to '1 Jan 1900' and get the same result like this:

Declare @date datetime = DateAdd(minute, 1748160, '1 Jan 1900');
Select DatePart(yy, @date) - 1900 as Years, DatePart(mm, @date) - 1 Months, DatePart(dd, @date) - 1 Days

So you can use this like:

DECLARE @dtStartDate date = '01 Jan 2015'
DECLARE @dtEndDate date = '11 Jan 2015'
Declare @date datetime = DateAdd(minute, DATEDIFF(minute, @dtStartDate, @dtEndDate), '1 Jan 1900');
Select DatePart(yy, @date) - 1900 as Years, DatePart(mm, @date) - 1 Months, DatePart(dd, @date) - 1 Days

Need Your Help

Also check realloc() if shrinking allocated size of memory?

c++ c realloc

When you call realloc() you should check whether the function failed before assigning the returned pointer to the pointer passed as a parameter to the function...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.