Получение даты из 4-значного странного формата даты в Oracle SQL

Я получаю строку от декодирования codebar-128, как только я анализирую все данные в прочитанном коде, я получаю дату в странном 4-значном формате: «ГДДД»

Цифра «Y» представляет собой последнюю цифру года (0-9). Цифры «DDD» представляют день года (1-366).

Проблема в неоднозначном значении Года. Правило для решения этой проблемы должно быть следующим:

  1. Год, вычисленный для цифры «Y», должен быть ближайшим годом к году Sysdate.
  2. Никогда разница между годом Sysdate и вычисленным годом для цифры «Y» не будет больше 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 oracle sql-date-functions to-date sql-function
person Trimax    schedule 26.06.2020    source источник
comment
Если применяются ваши правила (1) и (2), почему «1» становится 2001, а не 2021? Это не ближе к сегодняшнему дню и более чем через 4 года? Я не уверен, что это просто проблема с тем, как вы это создали. Может быть полезно показать ожидаемый результат, скопировав то, что вы на самом деле получили, и внеся необходимые изменения, возможно, объяснив каждое из них?   -  person Alex Poole    schedule 26.06.2020
comment
@AlexPoole, ты в порядке! «1» должно быть «2021». Я исправил это.   -  person Trimax    schedule 26.06.2020
comment
Излишне упоминать, что вы должны изменить свой дизайн на правильный тип данных DATE (или TIMESTAMP) как можно скорее.   -  person Wernfried Domscheit    schedule 26.06.2020

Ответы (3)


arrow_upward
1
arrow_downward

Разделите его на несколько предложений с помощью, чтобы его было легче понять, вы можете объединить его в один запрос, если хотите.

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
person Koen Lostrie    schedule 26.06.2020

arrow_upward
2
arrow_downward

Вы можете сравнить однозначное значение с последней цифрой текущего года и, если разница больше 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

Я не проверял поведение будущего года, поэтому вам, вероятно, нужно протестировать и скорректировать его по мере необходимости.

person Alex Poole    schedule 26.06.2020

arrow_upward
1
arrow_downward

Это должно дать вам некоторые идеи:

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);

Если вам нужно преобразовать во многие переменные (многие), я советую создать функцию DETERMINISTIC.

С Уважением.

person KOBER    schedule 26.06.2020
comment
Я не уверен, что это следует правилам; он по-прежнему дает 2001 для вашего примера, а не 2021? Преобразование 1 в 01, похоже, мало что добавляет (и может быть выполнено с помощью простой конкатенации строк), но не уверен, что это имеет значение в более широкой картине. - person Alex Poole; 26.06.2020
comment
Прошу прощения, проверьте еще раз, я пропустил часть логики в предыдущем примере. - person KOBER; 26.06.2020