Teradata-年度等价

发布于 2025-01-28 15:57:08 字数 199 浏览 3 评论 0 原文

我很难找到与Teradata相当的东西（Excel）。我弄乱了以下内容，但希望我希望它显示一年的一部分。因此，我不想看到37.033。如果可能的话，它可以考虑LEAP年的时间，所以不想将其除以365。任何帮助将不胜感激！

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぶ宁プ宁ぶ 2025-02-04 15:57:08

没有直接功能来获取所需的输出。
Excel YearFrac方法使用不同的逻辑来基于可选参数基础来计算输出。

语法年frac（start_date，end_date，[basis]）

将基本参数视为0或省略，您可以使用以下查询在Teradata中实现它。

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

具有0或省略的基础参数使用30/360格式来计算差异。
您可以在下面的链接中找到有关年度FRAC逻辑的更多详细信息。

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

回复收藏 0 原文

~没有更多了~