A person at home, nothing to do, suddenly think of a long time did not write things, so ...
Often when you calculate on two dates, not only do you want to have a separate year or year, get the details, the date results are accurate to the day, the following last function and a procedure are used to complete this function.
First function version
Create function [dbo]. [Getyearmonthdayfun] (
@begin datetime,
@end datetime)
Returns varchar (50)
As
Begin
/*
* Function: Calculate the difference of two dates *
* Author: charry0110 *
* Description: Returns the date, the result is accurate to the day, the parameter is GETDATE () calculates the birth information *
*/
DECLARE @Ageyear int
DECLARE @Agemonth int
DECLARE @Ageday int
Set @Ageyear =datediff (year, @begin, @end)-
Case when DateAdd (year, @begin, @end), @begin) > @end
Then 1 else 0 end
Set @Agemonth =ltrim (DateDiff (month, @begin, @end)-
Case when DateAdd (Month,datediff (month, @begin, @end), @begin) > @end
Then 1 else 0 end) -12* @Ageyear
if (@begin) <day (@end) or day (@begin) =day (@end))
Set @Ageday =day (@end)-day (@begin)
Else
Set @Ageday =datediff (LTrim (@end) + '-'
+ltrim (Month (DATEADD (mm,-1, @end))) + '-' +ltrim (Day (@begin))),
(LTrim (@end)) + '-' +ltrim (month (@end)) + '-' +ltrim (Day (@end))))
Return LTrim (@Ageyear) + ' year ' +ltrim (@Agemonth) + ' month ' +ltrim (@Ageday) + ' Day '
/*
--Example 1
SELECT dbo. Getyearmonthdayfun (' 2008-6-21 ', GETDATE ())
--Example 2
SELECT dbo. Getyearmonthdayfun (crdate,refdate) from sysobjects
--Example 3
SELECT dbo. Getyearmonthdayfun (Crdate,getdate ()) from sysobjects
*/
End
Second stored procedure version
CREATE PROCEDURE [dbo]. [Getyearmonthdaypro]
(
@begin datetime,
@end datetime
)
As
Begin
/*
* Function: Calculate the difference of two dates *
* Author: charry0110 *
* Description: Returns the date, the result is accurate to the day, the parameter is GETDATE () calculates the birth information *
*/
DECLARE @Ageyear int
DECLARE @Agemonth int
DECLARE @Ageday int
Set @Ageyear =datediff (year, @begin, @end)-
Case when DateAdd (year, @begin, @end), @begin) > @end
Then 1 else 0 end
Set @Agemonth =ltrim (DateDiff (month, @begin, @end)-
Case when DateAdd (Month,datediff (month, @begin, @end), @begin) > @end
Then 1 else 0 end) -12* @Ageyear
if (@begin) <day (@end) or day (@begin) =day (@end))
Set @Ageday =day (@end)-day (@begin)
Else
Set @Ageday =datediff (LTrim (@end) + '-'
+ltrim (Month (DATEADD (mm,-1, @end))) + '-' +ltrim (Day (@begin))),
(LTrim (@end)) + '-' +ltrim (month (@end)) + '-' +ltrim (Day (@end))))
Select LTrim (@Ageyear) + ' year ' +ltrim (@Agemonth) + ' month ' +ltrim (@Ageday) + ' Day '
/*
--Example 1
exec getyearmonthdaypro ' 2008-6-21 ', ' 2008-7-21 '
*/
End