sql - Oracle - 如何转换由 NUMBER(6,0) 格式表示的日期
问题描述
我从我们的第三方合作伙伴那里获得了数据,每个日期列都以这种 NUMBER(6,0) 格式编码:
118346
118347
118348
118351
119013
119035
119049
119051
118339
118353
119019
119028
119029
119031
最后 3 位数字都不超过 365,所以我认为118339
必须是 2018 + 339 天,即 2018 年 12 月 5 日:'2018-12-05'
。这种格式我从来没有遇到过,所以我有点无奈如何处理。这是一些标准化的格式吗?我可以使用一些内置的转换功能,还是应该使用一些算术手动剪切和转换它?
我想按周对行分组进行排序,所以也许我什至不应该转换它,但出于某种原因,我觉得转换为日期类型会更优雅。哪种方法更好?
编辑:我刚刚检查了我的 excel 版本的数据,这种格式实际上就像我想象的那样工作。所以问题是成立的。
解决方案
这似乎是Excel 基于 1900 年的 dates 内部表示。假设您的解释是正确的,您可以通过一些操作将其转换为正常日期:
-- CTE for sample values
with your_table (num) as (
select *
from table(sys.odcinumberlist(118339, 118346, 118347, 118348, 118351, 119013, 119035,
119049, 119051, 118339, 118353, 119019, 119028, 119029, 119031))
)
-- actual query
select num,
date '1899-12-31'
+ floor(num/1000) * interval '1' year
+ mod(num, 1000) * interval '1' day as converted
from your_table;
NUM CONVERTED
---------- ----------
118339 2018-12-05
118346 2018-12-12
118347 2018-12-13
118348 2018-12-14
118351 2018-12-17
119013 2019-01-13
119035 2019-02-04
119049 2019-02-18
119051 2019-02-20
118339 2018-12-05
118353 2018-12-19
119019 2019-01-19
119028 2019-01-28
119029 2019-01-29
119031 2019-01-31
这将前三位数字 - 获得floor(num/1000)
- 视为从 1900 年偏移的年数。这些数字乘以单个年份间隔值,得到 118 或 199 年。然后它将最后三位数字 - 从mod(num, 1000)
- 视为该年的天数,乘以单日间隔。然后将两者都添加到固定日期 1899-12-31。(您可以改用 1900-01-01 但最后必须减去一天...)
推荐阅读
- http - Nginx config静态网站显示404 Not found
- flutter - 提供者与 Get_it
- haskell - 我可以“分解”类型签名吗?
- python - 如果在 python 的 socket recv 方法上 10 分钟内没有收到数据,如何设置关闭套接字的超时并杀死线程?
- python-3.x - “TypeError:'NoneType' 对象不可下标”是什么意思?
- python - 如何将变量从父类传递给子类?
- angular - 对于 angular 中的自定义自动完成功能,模糊事件无法按预期工作
- c# - System.MissingMethodException:'找不到方法:'无效 Microsoft.AspNetCore.Mvc.RazorPages.RazorPagesOptions.set_AllowAreas(Boolean)'。
- javascript - 为什么点击时会从 iframe 中删除属性?
- sql - 使用两个更新,其中第二个更新语句使用第一个更新语句中的列作为输入