将十进制年份转换为日期 [英] Convert decimal year to date
问题描述
我在表中的日期以存储为小数年.一个示例是2003.024658
,它转换为January 9, 2003
.
我想将十进制年份转换为Oracle的日期格式.
I have dates in a table that are stored as decimal years. An example is 2003.024658
which translates to January 9, 2003
.
I would like to convert the decimal years to Oracle's date format.
I've found someone who's done this in Excel: Decimal year to date formula?
=DATE(INT(B1),1,MOD(B1,1)*(DATE(INT(B1)+1,1,1)-DATE(INT(B1),1,1)))
但是,我不太清楚如何将逻辑转换为Oracle PL/SQL.
However, I can't quite figure out how to convert the logic to Oracle PL/SQL.
推荐答案
如果从假定小数部分是根据给定年份的天数(即365或366(取决于是否是a年),您可以执行以下操作:
If you start from the assumption that the decimal portion was calculated according to the number of days in the given year (i.e. 365 or 366 depending on whether it was a leap year), you could do something like this:
with
q1 as (select 2003.024658 d from dual)
,q2 as (select d
,mod(d,1) as decimal_portion
,to_date(to_char(d,'0000')||'0101','YYYYMMDD')
as jan01
from q1)
,q3 as (select q2.*
,add_months(jan01,12)-jan01 as days_in_year
from q2)
select d
,decimal_portion * days_in_year as days
,jan01 + (decimal_portion * days_in_year) as result
from q3;
d: 2003.024658
days: 9.00017
result: 10-JAN-2003 12:00am
这篇关于将十进制年份转换为日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!