SQL calculation date difference, accurate to day

Source: Internet
Author: User
Tags datetime getdate

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.