将字符串转换为日期字段为SQL Oracle
我需要将字符串转换为日期字段。该字段存储30个字符。日期如果存在,则格式为“yyyymmdd”(20170202)。在所有情况下,日期之后有22个空格。我需要将此字段格式化为日期字段,如下所示:dd-mm-yyyy。将字符串转换为日期字段为SQL Oracle
我试过几个公式: TO_CHAR(PERSACTION.NEW_VALUE_02, 'dd-mm-yyyy')
,TO_CHAR(PERSACTION.NEW_VALUE_02, 'yyyymmdd')
,trim(TO_CHAR(PERSACTION.NEW_VALUE_02, 'yyyymmdd'))
错误信息:无效的数字格式模型。欢迎您的专业知识和赞赏。
曾尝试转换为日期格式,然后再次char?
TO_CHAR(TO_DATE(PERSACTION.NEW_VALUE_02,'yyyymmdd'),'dd-mm-yyyy')
如果OP想要转换为** date **,为什么需要'TO_CHAR'? – mathguy
因为它需要是char。如Tania所问,该值存储在日期字段中的文本字段中。 –
to_char(to_date(rtrim(new_value_02), 'yyyymmdd'), 'dd-mm-yyyy')
应该做的伎俩。 rtrim删除字符串右侧的空格。然后使用指定的日期格式将其转换为日期,然后再以所需的格式将其转换为字符串。
如果OP想要转换为**日期**,为什么需要'TO_CHAR'? – mathguy
这工作完美,我会保留这个以备将来使用,非常感谢你 –
@mathguy好点。我加了这个,因为他特别提到了日期格式。但数据类型Date中的日期没有格式,它只是特定日期的表示。只需将to_char留出,你就会有一个日期。 –
请请请不要存储DATE和CHARACTER数据类型。这只会导致使用DATE数据类型时可以避免的问题。
如果你想在串改变 20170202另一个串并没有实际的日期(这将有没有内在的格式化文本表示),你可以选择使用正则表达式来改变,而不是将它,日期和背部:
select regexp_replace('20170202 ', '^(\d{4})(\d{2})(\d{2}) +$', '\3-\2-\1')
from dual;
REGEXP_REPLACE(
---------------
02-02-2017
或者你可以使用substr
代替regexp_substr
,它可以执行,即使你要调用它的三倍更好;使用CTE只是为了避免重复值:
with t(str) as (
select '20170202 ' from dual
)
select substr(str, 7, 2) ||'-'|| substr(str, 5, 2) ||'-'|| substr(str, 1, 4)
from t;
SUBSTR(STR
----------
02-02-2017
如果转换为日期和背部,你会发现它不能转换的任何值,因为它们会导致异常被抛出。这意味着你有不好的数据;当然,通过首先使用正确的数据类型可以避免这种情况。这些将会转换任何旧垃圾,取决于字符串偏离您期望的模式的程度 - 但包括代表无效日期的'20170231'之类的字符串。只有空格的空值或字符串将被转换为与substr版本不同的东西,但是可以将其过滤掉。
你可以看到的那种变化,你会带一些样本数据得到不符合您的期望:
with t(str) as (
select '20170202 ' from dual
union all select '20170231 ' from dual
union all select '2017020c ' from dual
union all select '2017020 ' from dual
union all select '201702021 ' from dual
union all select ' ' from dual
union all select null from dual
)
select str,
regexp_replace(str, '^(\d{4})(\d{2})(\d{2}) +$', '\3-\2-\1') as reg,
substr(str, 7, 2) ||'-'|| substr(str, 5, 2) ||'-'|| substr(str, 1, 4) as sub
from t;
STR REG SUB
------------- ------------- -------------
20170202 02-02-2017 02-02-2017
20170231 31-02-2017 31-02-2017
2017020c 2017020c 0c-02-2017
2017020 2017020 0 -02-2017
201702021 201702021 02-02-2017
- -
--
随着主持人和空白的期望,正则表达式不修改任何东西,没有按完全由8个数字字符组成。但它仍然可能形成无效的“日期”。
不明白,你说这个字段是日期,但可以保存30个字符。请包括表格定义。 – Alfabravo
您想将字符串**转换为日期**。从你的标题和你的文章精确引用。现在,Oracle具有'TO_CHAR'和'TO_DATE'功能。如果你想将**转换为日期**,那么这两个函数中的哪一个(同样,它们的名字是'TO_CHAR'和'TO_DATE'),你认为它更可能是正确的吗? – mathguy
该表是一个字符串(30)。我同意,我希望我们能够控制数据如何存储在表格中,但可悲的是我们没有。 –