Monday, 27 May 2019

Get days, months, and years between dates in SQL

Example:-












Select Command:-

SELECT * FROM dbo.GetDateDifferenceInYearsMonthsDays('2019-04-27 10:24:55.387',GETDATE())

Create SQL Function:-

CREATE FUNCTION [dbo].[GetDateDifferenceInYearsMonthsDays]
(
    @FromDate DATETIME, @ToDate DATETIME
)
RETURNS
 @DateDifference TABLE (
 YEAR INT,  MONTH INT, DAYS INT)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
              @FromDate) > @ToDate THEN 1 ELSE 0 END)
   
    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
   
    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
   
    INSERT INTO @DateDifference
    VALUES(@Years, @Months, @Days)
   
    RETURN
END

No comments:

Post a Comment

What is Agile,advantage and disadvantages

Introduction:- It is a software development life cycle used for software development, that is characterized by the division of tasks in...