使用SQL计算以年为单位的确切日期差 [英] Calculate exact date difference in years using SQL

问题描述

我收到的报告的数据自动是 ETL 到数据库。我提取并转换某些数据以将其加载到其他位置。我需要做的一件事是 DATEDIFF ，但是年份需要准确（即4.6年，而不是四舍五入。）

I receive reports in which the data is ETL to the DB automatically. I extract and transform some of that data to load it somewhere else. One thing I need to do is a DATEDIFF but the year needs to be exact (i.e., 4.6 years instead of rounding up to five years.

以下是我的脚本：

select *, DATEDIFF (yy, Begin_date, GETDATE()) AS 'Age in Years'
from Report_Stage;

' Age_In_Years的列已四舍五入。如何获取以年为单位的确切日期？

The 'Age_In_Years' column is being rounded. How do I get the exact date in years?

推荐答案

有您尝试以月为单位求出差额，然后以这种方式计算年数？例如30个月/ 12将为2.5年。

Have you tried getting the difference in months instead and then calculating the years that way? For example 30 months / 12 would be 2.5 years.

编辑：此SQL查询包含几种方法计算日期差：

This SQL query contains several approaches to calculate the date difference:

SELECT CONVERT(date, GetDate() - 912) AS calcDate
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) diffDays
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) / 365.0 diffDaysCalc
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) diffMonths
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) / 12.0 diffMonthsCalc
      ,DATEDIFF(YEAR, GetDate() - 912, GetDate()) diffYears

这篇关于使用SQL计算以年为单位的确切日期差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！