Teradata - YEARFRAC equivalence

Question

I am having a hard time trying to find something that would be equivalent to YEARFRAC (Excel) for Teradata. I messed around with the below, but want I want it to display the fraction of the year. So instead of 37 I would want to see 37.033. If possible would like it to account for leap years so wouldn't want to just divide it by 365. Any help would be greatly appreciated!

SELECT  (CURRENT_DATE - CAST('1985-05-01' AS DATE)) YEAR

Answer 1

There is no direct function to get the desired output. Excel YEARFRAC method uses different logic to calculate the output based on the optional parameter basis.

Syntax YEARFRAC(start_date, end_date, [basis])

Considering the basis parameter as 0 or omitted, you can achieve it in Teradata using below query.

SELECT 
DATE'2022-05-13' AS Till_Date
,DATE'1985-05-01'  AS From_Date
,(Till_Date - From_Date)  YEAR TO MONTH AS Year_To_Month
,EXTRACT(YEAR FROM Year_To_Month)
        +EXTRACT(MONTH FROM Year_To_Month)*30.0000/360
        +( EXTRACT(DAY FROM Till_Date)-EXTRACT(DAY FROM From_Date))*1.0000/360 AS YEARFRAC

The basis parameter with 0 or omitted uses a 30/360 format to calculate the difference. You can find more details about the YEARFRAC logic in below link.

https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8