从oraclesql中的4位奇怪日期格式获取日期

plicqrtu 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(421)

我从解码codebar-128收到一个字符串，一旦我解析了所读代码中的所有数据，我就会得到一个奇怪的4位数格式的日期：“yddd”
“y”数字表示一年中的最后一个数字（0-9）。“ddd”数字表示一年中的某一天（1-366）。
问题是年份的模糊值。解决这一问题的规则必须如下：
为“y”数字计算的年份必须是距离sysdate年份最近的年份。
“y”数字的sysdate year和computed year之差永远不会大于4。
我的代码：

SELECT SYSDATE, TO_DATE('0213', 'YDDD'), TO_DATE('1212', 'YDDD'), 
        TO_DATE('2212', 'YDDD'), TO_DATE('3212', 'YDDD'), TO_DATE('4213', 'YDDD'),
        TO_DATE('6212', 'YDDD'), TO_DATE('7212', 'YDDD'), TO_DATE('8213', 'YDDD'),
        TO_DATE('9212', 'YDDD')
FROM dual;

这是我需要得到的：

+-----------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
|  SYSDATE  | TO_DATE('20213','YYDDD') | TO_DATE('21212','YYDDD') | TO_DATE('22212','YYDDD') | TO_DATE('23212','YYDDD') | TO_DATE('24213','YYDDD') | TO_DATE('16213','YYDDD') | TO_DATE('17212','YYDDD') | TO_DATE('18212','YYDDD') | TO_DATE('19212','YYDDD') |
+-----------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
| 26-JUN-20 | 31-JUL-20                | 31-JUL-21                | 31-JUL-22                | 31-JUL-23                | 31-JUL-24                | 31-JUL-16                | 31-JUL-17                | 31-JUL-18                | 31-JUL-19                |
+-----------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+

如你所见，如果我有一年倒数第二位的数字，就没有问题了。
这是我真正得到的：

+-----------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
|  SYSDATE  | TO_DATE('0213','YDDD') | TO_DATE('1212','YDDD') | TO_DATE('2212','YDDD') | TO_DATE('3212','YDDD') | TO_DATE('4213','YDDD') | TO_DATE('6212','YDDD') | TO_DATE('7212','YDDD') | TO_DATE('8213','YDDD') | TO_DATE('9212','YDDD') |
+-----------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
| 26-JUN-20 | 31-JUL-20              | 31-JUL-21              | 31-JUL-22              | 31-JUL-23              | 31-JUL-24              | 31-JUL-26              | 31-JUL-27              | 31-JUL-28              | 31-JUL-29              |
+-----------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+

sql to-date sql-function oracle sql-date-functions

来源：https://stackoverflow.com/questions/62591438/getting-a-date-from-a-4-digit-strange-date-format-in-oracle-sql

3条答案

按热度按时间

3mpgtkmj1#

将它拆分为多个with子句，以便更容易理解，如果需要，可以将它连接到单个查询中。

WITH sampledata (dt) AS
(
 SELECT '0213' FROM DUAL UNION
 SELECT '1212' FROM DUAL UNION
 SELECT '2212' FROM DUAL UNION
 SELECT '3212' FROM DUAL UNION
 SELECT '4213' FROM DUAL UNION
 SELECT '5213' FROM DUAL UNION
 SELECT '6212' FROM DUAL UNION
 SELECT '7212' FROM DUAL UNION
 SELECT '8213' FROM DUAL UNION
 SELECT '9212' FROM DUAL 
), parsed_sampledata (yr, ddd) AS
(
SELECT substr(d.dt,1, 1) + TO_CHAR(SYSDATE,'YY') as yr, substr(d.dt,2,3) as ddd
  FROM sampledata d
)
SELECT TO_DATE(ddd||yr - (CASE WHEN yr - TO_CHAR(SYSDATE,'YY') < 5 THEN 0 ELSE 10 END),'DDDYY')  
  FROM parsed_sampledata d;

31-JUL-2020
31-JUL-2021
31-JUL-2022
31-JUL-2023
31-JUL-2024
01-AUG-2015
30-JUL-2016
31-JUL-2017
01-AUG-2018
31-JUL-2019

赞(0）回复(0）举报 2021-07-24

pdtvr36n2#

您可以将单个数字值与当前年份的最后一个数字进行比较，如果差值大于4，则调整10年。但它需要双向发展；一旦“今天”是2026年，你就要多活10年。

select column_value as val,
  to_date(column_value, 'YDDD') as dt1,
  to_number(substr(column_value, 1, 1)) as y,
  mod(extract(year from sysdate), 10) as yy,
  case
    when to_number(substr(column_value, 1, 1)) - mod(extract(year from sysdate), 10) > 4 then -10
    when mod(extract(year from sysdate), 10) - to_number(substr(column_value, 1, 1)) > 4 then 10
    else 0
  end as adj,
  to_date(column_value, 'YDDD')
    + case
        when to_number(substr(column_value, 1, 1)) - mod(extract(year from sysdate), 10) > 4 then -10
        when mod(extract(year from sysdate), 10) - to_number(substr(column_value, 1, 1)) > 4 then 10
        else 0
      end * interval '1' year as dt2,
  add_months(to_date(column_value, 'YDDD'),
    12 * case
        when to_number(substr(column_value, 1, 1)) - mod(extract(year from sysdate), 10) > 4 then -10
        when mod(extract(year from sysdate), 10) - to_number(substr(column_value, 1, 1)) > 4 then 10
        else 0
      end) as dt2
from table(sys.odcivarchar2list('0213', '1212', '2212', '3212', '4213',
                                '5212', '6212', '7212', '8213', '9212'));

这就得到了

VAL  DT1                 Y         YY        ADJ DT2        DT2       
---- ---------- ---------- ---------- ---------- ---------- ----------
0213 2020-07-31          0          0          0 2020-07-31 2020-07-31
1212 2021-07-31          1          0          0 2021-07-31 2021-07-31
2212 2022-07-31          2          0          0 2022-07-31 2022-07-31
3212 2023-07-31          3          0          0 2023-07-31 2023-07-31
4213 2024-07-31          4          0          0 2024-07-31 2024-07-31
5212 2025-07-31          5          0        -10 2015-07-31 2015-07-31
6212 2026-07-31          6          0        -10 2016-07-31 2016-07-31
7212 2027-07-31          7          0        -10 2017-07-31 2017-07-31
8213 2028-07-31          8          0        -10 2018-07-31 2018-07-31
9212 2029-07-31          9          0        -10 2019-07-31 2019-07-31

我还没有验证未来一年的行为，所以你可能需要测试和调整，如有必要。

赞(0）回复(0）举报 2021-07-24

u0sqgete3#

这会给你一些想法：

WITH DATES_LIST AS
(
 SELECT '0213' AS D FROM DUAL UNION
 SELECT '1212' AS D FROM DUAL UNION
 SELECT '2212' AS D FROM DUAL UNION
 SELECT '3212' AS D FROM DUAL UNION
 SELECT '4213' AS D FROM DUAL UNION
 SELECT '5213' AS D FROM DUAL UNION
 SELECT '6213' AS D FROM DUAL UNION
 SELECT '7212' AS D FROM DUAL UNION
 SELECT '8212' AS D FROM DUAL UNION
 SELECT '9212' AS D FROM DUAL 
)
SELECT  TO_DATE(REGEXP_REPLACE(D,'^\d{1}',
        CASE WHEN BOTT_R <= UPP_R THEN BOT ELSE UPP END),'YYDDD') AS YEAR 
        FROM (
select D,(TO_CHAR(SYSDATE,'RR') - 10) + regexp_substr(D, '^\d{1}') BOT,
       ABS((TO_CHAR(SYSDATE,'RR') - 10) + regexp_substr(D, '^\d{1}')-TO_CHAR(SYSDATE,'RR')) BOTT_R,
       TO_CHAR(SYSDATE,'RR') + regexp_substr(D, '^\d{1}') UPP,
       (TO_CHAR(SYSDATE,'RR') + regexp_substr(D, '^\d{1}')) - TO_CHAR(SYSDATE,'RR') UPP_R
        from DATES_LIST);

如果你需要转换成许多变量，我的建议是创建一个确定性函数。
当做。

赞(0）回复(0）举报 2021-07-24

我来回答

从oraclesql中的4位奇怪日期格式获取日期

3条答案

相关问题

热门标签

最新问答