是否有与Progress 4GL INTERVAL功能相同的SQL Server功能？

I am aware that Progress 4GL version 10 onwards is equipped with an very clever integrated function called 'INTERVAL' which returns the time interval between two DATE, DATETIME, or DATETIME-TZ values. If you use the following syntax,

我知道Progress 4GL版本10以后配备了一个非常聪明的集成函数,称为“INTERVAL”,它返回两个DATE,DATETIME或DATETIME-TZ值之间的时间间隔。如果使用以下语法,

> DISPLAY INTERVAL(05/06/2014, 05/05/2015, "years").

would give you zero as it is one day short for it to be counted as one year.

会给你零,因为它被计为一年的短暂一天。

But in SQL, the closet function can be used in relation to this would be DATEDIFF and you would not be able to get the same result. In SQL Server the following query,

但是在SQL中,可以使用closet函数来处理这个DATEDIFF并且你将无法获得相同的结果。在SQL Server中,以下查询,

PRINT DATEDIFF(YEAR, '05/06/2014', '05/05/2015');

PRINT DATEDIFF(年,'05 / 06/2014','05 / 05/2015');

would still print one instead of zero ignoring the bit that it is still one day short to be counted as a year. Therefore my question is would there be an integrated function in SQL which could be used to achieve the same result like Progress 4GL ?

仍然会打印一个而不是零,忽略它仍然有一天短暂被计为一年。因此,我的问题是SQL中是否有一个集成函数可用于实现与Progress 4GL相同的结果?

2 个解决方案

#1

DATEDIFF returns the number of specified boundaries crossed between the 2 dates. This works well for days, but is less useful for months and years,

DATEDIFF返回两个日期之间交叉的指定边界数。这种方法效果好几天,但几个月和几年都不太有用,

Below is one method to calculate the differences in years in T-SQL, as in a common age calculation. It's not very robust but gets the job done. The core issue is that SQL Server doesn't have an interval data type.

下面是计算T-SQL中年份差异的一种方法,如在常见年龄计算中。它不是很强大,但完成工作。核心问题是SQL Server没有间隔数据类型。

--using YYYY-MM-DD date format literal to avoid ambiguity
DECLARE @date1 date = '2014-05-06', @date2 date = '2015-05-05';

--years between 2 dates
SELECT (CAST(CONVERT(char(8), @date2, 112) AS int) - CAST(CONVERT(char(8), @date1, 112) AS int)) / 10000;

#2

In general, for this sort of problem you can calculate a figure at a finer granularity and then resolve it to a coarser one. In this case, you can figure out the number of days between the two dates and then divide by 365 and floor the result.

通常,对于这类问题,您可以以更精细的粒度计算数字,然后将其解析为更粗略的数字。在这种情况下,您可以计算两个日期之间的天数,然后除以365并将结果置于最低点。

DECLARE @date1 date = '2014-05-06', @date2 date = '2015-05-05';

SELECT FLOOR(DATEDIFF(DAY, @date1, @date2)/365.0)

Of course, leap years present a bit of a challenge. But there's the general idea.

当然,闰年会带来一些挑战。但是有一般的想法。

#1