如何将这些儒略日期转换为 oracle 日期格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24666390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert these julian dates to oracle date format?
提问by JohnD
Below is a sample set of data that is supposed to be dates. I am not sure what kind of format it should be but I was told they are julian dates.
下面是一组应该是日期的示例数据。我不确定它应该是哪种格式，但有人告诉我它们是朱利安日期。
DATE
92017
92320
99002
99003
112010
112011
112012
112013
Can anyone convert them into oracle dates? I tried
任何人都可以将它们转换为 oracle 日期吗？我试过
select to_date(DATE,'J') from dual
but some results were in the 1950s and 1940s which doesn't seen right for the data we are dealing with. Am i doing it right here?
但有些结果出现在 1950 年代和 1940 年代，这对于我们正在处理的数据来说并不合适。我在这里做吗？
I also tried this formula from this link: http://www.kirix.com/stratablog/jd-edwards-date-conversions-cyyddd
我也从这个链接尝试了这个公式：http: //www.kirix.com/stratablog/jd-edwards-date-conversions-cyyddd
SELECT
TO_DATE(1900+(DATE/1000),1,1)+
TO_NUMBER(SUBSTR(TO_CHAR(DATE),4))-1 FROM DUAL;
Thanks
谢谢
采纳答案by Bob
-- Using JD Edwards Date Conversions if DATE is a number
-- 如果 DATE 是数字，则使用 JD Edwards 日期转换
select to_date(to_char(1900 + floor(DATE / 1000)),'YYYY') + mod(DATE,1000) - 1 from dual;
-- Using JD Edwards Date Conversions if DATE is a string
-- 如果 DATE 是字符串，则使用 JD Edwards 日期转换
select to_date(to_char(1900 + floor(to_number(DATE) / 1000)),'YYYY') + mod(to_number(DATE),1000) - 1 from dual;
回答by Diego
If your date field is varchar:
如果您的日期字段是 varchar：
select TO_DATE(TO_CHAR(to_number('115032')+1900000),'YYYYDDD') jdedate from dual;
If your date fiel is a number:
如果您的日期字段是数字：
select TO_DATE(TO_CHAR(115032+1900000),'YYYYDDD') jdedate from dual;
Both Return:
两者都返回：
JDEDATE
-----------
01/02/2015
回答by Swastik Raj Ghosh
The simplest way that I found to convert from Julian is: -
我发现从朱利安转换的最简单的方法是：-
SELECT TO_CHAR(TO_DATE(your_julian_date,'JSP'),'dd-Mon-yyyy AD') AS DAY FROM dual;
Also, doesn't matter if the date field is number or a String. If a string, simply put the Julian date inside single quotes.
此外，日期字段是数字还是字符串都没有关系。如果是字符串，只需将 Julian 日期放在单引号内。
Also, the Oracle definition of Julian is 'number of days since January 1, 4712 BC'. (Many have erroneously commented as 4713 BC). Ref: https://asktom.oracle.com/pls/apex/asktom.search?tag=julian-date
此外，Oracle 对 Julian 的定义是“自公元前 4712 年 1 月 1 日以来的天数”。（许多人错误地评论为公元前 4713 年）。参考：https: //asktom.oracle.com/pls/apex/asktom.search?tag =julian- date
回答by Jahanzeb Kibria
Simple
简单的
select to_date('0101'|| substr(TRN_REF_NO, 8,2), 'DDMMYY') + to_number(substr(TRN_REF_NO, 10,3)) - 1 Trn_dt
