SQL Server Calculate Number of years between dates in Sql

31moq8wy 于 2023-03-17 发布在其他

关注(0)|答案(3)|浏览(132)

I need to calculate the number of complete years between two dates in sql. The query I am using currently to calculate is

--Date1 > Date2
CASE
WHEN YEAR(Date1) = YEAR(Date2)
    THEN DATEDIFF(year, Date2, Date1)
WHEN YEAR(Date1) > YEAR(Date2) 
        AND MONTH(Date1) >= MONTH(Date2)
        AND DAY(Date1) >= DAY(Date2)
    THEN DATEDIFF(year, Date2, Date1)
ELSE
    DATEDIFF(year, Date2, Date1) - 1

However I need to tweak it somehow so that it considers the time between 1/5/2011 and 30/4/2012 as 1 complete year.

sql-server

来源：https://stackoverflow.com/questions/8400478/calculate-number-of-years-between-dates-in-sql

3条答案

按热度按时间

h7appiyu1#

Add one day to Date2 before you compare it:

dateadd(day, 1, Date2)

赞(0）回复(0）举报 2023-03-17

drkbr07n2#

If the goal is to consider 1-year-less-a-day to be a full year, what about simply adding 1 day to your later date? dateadd() should do it.

mysql> SELECT ADDDATE('2011-05-01', INTERVAL 1 DAY);
        -> '2011-05-02'

I don't think that would cause other erroneous calculations...

赞(0）回复(0）举报 2023-03-17

bbuxkriu3#

This isn't totally accurate, but it's close: It calculates the difference in days, divides by 365.25, then rounds down.

DECLARE @d1 datetime = '2000-01-01', @d2 datetime = GETDATE();
SELECT CAST(ROUND(DATEDIFF(DAY, @d1, @d2)/365.25, 0, 1) AS INT);

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Calculate Number of years between dates in Sql

3条答案

相关问题

热门标签

最新问答