从char更改日期格式 [英] change date format from char

问题描述

我在indicator_date字段中具有以下格式. indicator_date字段是char数据类型.查询该列时，是否可以用mm/dd/yyyy格式标准化显示它们?允许用户手动输入日期，但是他们选择了日期，但我无法更改.谢谢.

I have the following formats in an indicator_date field. The indicator_date field is char data type. Is there a way to display them standardized in mm/dd/yyyy format when I query that column? The users are allowed to manually type the date however they choose and I cannot change that. Thanks.

日期显示方式示例:

5/2/2018
,5/21/2018
,01/29/2018

我尝试了to_date(INDICATOR_DATE,'mm/dd/yyyy')，但它返回了错误.

I've tried to_date(INDICATOR_DATE,'mm/dd/yyyy') but it returns an error.

ORA-01830:日期格式图片在转换整个输入之前结束 字符串

ORA-01830: date format picture ends before converting entire input string

推荐答案

对于无效的日期(20118年)，不可能编写出良好的转换表达式...但是对于其他方法，可能是:

For invalid dates (year 20118) it's not possible to write a good conversion expression ... but for the rest the approach could be:

  with dats as (
             select '5/2/2018' dt from dual
  union all select '5/21/2018' dt from dual
  union all select '01/29/2018' dt from dual
  union all select '2018-01-13' dt from dual
  union all select '8/17/20118' dt from dual
  union all select '23018' dt from dual
  )
  select dt
  , to_char(
     case when regexp_like(dt,'(0?[1-9]|[12][0-9]|3[01])/(0?[1-9]|1[0-2])/([12][0-9]{3,3})$') then to_date(dt,'DD/MM/YYYY') 
         when regexp_like(dt,'((0?[1-9]|1[0-2])/0?[1-9]|[12][0-9]|3[01])/([12][0-9]{3,3})$') then to_date(dt,'MM/DD/YYYY') 
         when regexp_like(dt,'([12][0-9]{3,3})-(0?[1-9]|1[0-2])-(0?[1-9]|[12][0-9]|3[01])$') then to_date(dt,'YYYY-MM-DD') 
  --       when regexp_like(dt,'^(0?[1-9]|1[0-2])(0?[1-9]|[12][0-9]|3[01])(([12][0-9])?[0-9]{2,2})$') then to_date(regexp_replace(dt,'^(0?[1-9]|1[0-2])(0?[1-9]|[12][0-9]|3[01])(([12][0-9])?[0-9]{2,2})$','\1/\2/\3') ,'MM/DD/YYYY') 
  else null end   
   , 'MM/DD/YYYY' ) dtconv 
  from dats
  ;

23018的问题是22818可以工作，但是2018年2月30日不存在.

Problem with 23018 was that 22818 would work but Feb 30 2018 is not existing.

这篇关于从char更改日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！