CODEWITHSUNDEEP
sql-serverprogress-4gl
Ravinath
Ravinath

Reputation: 65

Is there an SQL Server equivalent function to Progress 4GL INTERVAL function ?

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,

> 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,

PRINT DATEDIFF(YEAR, '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 ?

Upvotes: 0

Views: 1269

Answers (2)

Ben Thul
Ben Thul

Reputation: 32727

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.

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.

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46233

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,

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. 

--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;

Upvotes: 0

Related Questions