使用案例来了解日期格式是否正确

Question

我想迁移一个包含一些带有日期的列的表。问题是我的日期通常是dd/mm/yyyyy HH24:MM:YYYY格式。但有时格式似乎只是dd/mm/yyyy或空白。

我想这就是为什么我在尝试迁移数据时获得ORA-01830的原因。

我试过了

箱 当TO_DATE（MYDATE，'DD/MM/YYYY'）
然后TO_DATE（我的日期，'日/月/年 00：00：00'）
结尾为 MYDATE

但我不确定是否可以测试日期格式（当然它不起作用）。

谢谢

Answer 1

TO_DATE无法测试日期格式，但您可以这样做。如果拉利特的回答还不够，请尝试类似

select
  case when my_date like '__/__/__' then to_date(my_date, 'dd/mm/yy')
       when my_date like '__-__-__' then to_date(my_date, 'dd-mm-yy')
       ...
  end

Answer 2

所以你有data type问题。 DATE存储为 string literal 。正如您提到的，date model具有相同的DD/MM/YYYY部分，只是某些行缺少time部分或整个值NULL。

例如，假设您的表具有如下值 -

SQL> WITH dates AS(
  2  SELECT 1 num, '29/12/2014 16:38:57' dt FROM dual UNION ALL
  3  SELECT 2, '29/12/2014' FROM dual UNION ALL
  4  SELECT 3, NULL FROM dual
  5  )
  6  SELECT num, dt
  7  FROM dates
  8  /
       NUM DT
---------- -------------------
         1 29/12/2014 16:38:57
         2 29/12/2014
         3
SQL>

具有正确格式模型的TO_DATE应该可以解决问题。

让我们先坚持一个格式模型。

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.

现在，让我们使用 TO_DATE 将字符串文本显式转换为日期。

SQL> WITH dates AS(
  2  SELECT 1 num, '29/12/2014 16:38:57' dt FROM dual UNION ALL
  3  SELECT 2, '29/12/2014' FROM dual UNION ALL
  4  SELECT 3, NULL FROM dual
  5  )
  6  SELECT num, to_date(dt, 'dd/mm/yyyy hh24:mi:ss') dt
  7  FROM dates
  8  /
       NUM DT
---------- -------------------
         1 29/12/2014 16:38:57
         2 29/12/2014 00:00:00
         3
SQL>