我有一个表,用于在 VARCHAR 字段中存储日期。该字段中存储了几种格式:
DD-MMM-YY
和
MM/dd/yyyy hh:mm:ss
随着数据从不同来源进入表格,也许还会有更多。
我有一个检索这些的函数,我需要在从函数返回之前将它们重新格式化为相同的格式。在事先不知道日期的确切格式的情况下,我怎么能做到这一点?
我有一个在 VARCHAR 字段中存储日期的表格
将VARCHAR2更改为日期。
然后,您可以存储任何有效的日期值,而不考虑所需的传入格式,并对其执行任何 DATE 操作。
任何其他解决方案,将您的日期存储为日期以外的任何内容,只会导致更多问题。
例外情况是您需要几分之一秒(时间戳)或您需要时区(带时区的时间戳)
不容易。基本上,您必须知道使用的所有格式。如何?循序渐进。从最明显的开始(你已经提到的那些;请注意,分钟格式掩码是mi
,而不是mm
(几个月))。
该查询将失败很多次 - 有多少"错误"您尚未捕获。
REGEXP_LIKE
可能会有所帮助,因为您可以决定某些格式是否与您使用的掩码匹配,但是 - 转换"有效"格式时您无能为力,例如4 digits-two digits-two digits two digits:two digits:two digits
例如2019-87-54 69:74:84
(yyyy-mm-dd hh:mi:ss
)到有效日期,因为显然没有月份87,也没有第54天或第69小时等。
所以,慢慢来,一步一步来,经常测试。
使用CASE
语句和REGEXP_LIKE
来匹配不同的模式:
SELECT CASE
WHEN REGEXP_LIKE( your_column, '^d{1,2}[ /-](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)[ /-]d{4}$', 'i' )
THEN TO_DATE( your_column, 'dd mon yyyy' )
WHEN REGEXP_LIKE( your_column, '^d{1,2}[ /-](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)[ /-]d{2}$', 'i' )
THEN TO_DATE( your_column, 'dd mon yy' )
WHEN REGEXP_LIKE( your_column, '^(0?[1-9]|[12]d|3[01])[ /-](0?[1-9]|1[0-2])[ /-]d{4}$' )
THEN TO_DATE( your_column, 'dd mm yyyy' )
WHEN REGEXP_LIKE( your_column, '^(0?[1-9]|1[0-2])[ /-](0?[1-9]|[12]d|3[01])[ /-]d{4}$' )
THEN TO_DATE( your_column, 'mm dd yyyy' )
WHEN REGEXP_LIKE( your_column, '^(0?[1-9]|[12]d|3[01])[ /-](0?[1-9]|1[0-2])[ /-]d{2}$' )
THEN TO_DATE( your_column, 'dd mm rr' )
WHEN REGEXP_LIKE( your_column, '^(0?[1-9]|1[0-2])[ /-](0?[1-9]|[12]d|3[01])[ /-]d{2}$' )
THEN TO_DATE( your_column, 'mm dd rr' )
ELSE NULL
END AS your_column_date
FROM your_table
扩展它以添加不同的模式,如果您还需要包括时间。
但最好的解决方案是停止使用字符串并使用DATE
数据类型来存储日期值,并强制用户在输入数据时使用一致的格式,否则您将遇到问题,您可能会遇到01-02-03
的问题,它可能是 2nd 一月 2003, 1st 二月 2003 或 3rd 二月 2001,因为您不知道格式是否MM-DD-YY
,DD-MM-YY
或YY-MM-DD
.