Search code examples
sqldatediff

DATEDIFF function in the boundary conditions problem

Could anyone help me understand the following, the result of date difference in year is 0, but I think result should be 1 based on the condition, since the DATEADD function would return 2015-12-31, and it is greater than @ToDate 2015-01-01. here is the link for the article, the first example https://sqlhints.com/2015/07/10/how-to-get-difference-between-two-dates-in-years-months-and-days-in-sql-server/

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(YEAR, @FromDate, @ToDate)
   -
 (CASE
   WHEN DATEADD(YEAR, 
           DATEDIFF(YEAR, @FromDate,@ToDate), @FromDate)
       > @ToDate THEN 1 
         ELSE 0 END) 'Date difference in Years'

RESULT: enter image description here

Solution

  • This pretty obviously returns 0 to me:

    • DATEDIFF(YEAR, @FromDate, @ToDate) --> 1
    • DATEADD(YEAR, DATEDIFF(YEAR, @FromDate,@ToDate), @FromDate) --> '2015-12-31'
    • The CASE then returns 1

    So, 1-1 = 0.