ORACLE 判断字符型能否转换成日期型 ORA-01847 ORA-01843

ORACLE 判断字符型能否转换成日期型 ORA-01847 ORA-01843

判断字符型日期是否是合法的日期格式

ORA-01847 ORA-01843

日月明王的BLOG http://sunmoonking.spaces.live.com

有一用户的表需要导入到我们系统中，原表TM_WWM中日期是VARCHAR（8）型，格式为yyyymmdd。在导入到我系统之前需要先看看日期是否合法

• SQL1

select to_date(SALEDATE,'YYYYMMDD') from TM_WWM b

*

ERROR at line 1:

ORA-01847: day of month must be between 1 and last day of month

从提示可以看到字符型的日期中有非法字符，比如00天，或2月30日之类的。一开始想做一个日期表，存放几十年没月的开始日期结束日期，然后把这两个表中日期字段进行比较来判断是否合法。再或者不用中间表，用小时候学到的判断大小月的方法，1，3，5，7，8，10，腊，这7个月是31天，直接写SQL比较，2月单独判断。但是这两个方法都是比较死板的，于是想到用自己和自己比较的方法，如下。

• SQL2

select count(*) from TM_WWM

where saledate is not null

and substr(saledate,1,4) between 1970 and 2008

and substr(saledate,5,2) between '01' and '12'

and SALEDATE

between to_char(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),'YYYYMMDD')

AND to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1),'YYYYMMDD')

AND to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1),'YYYYMMDD')

*

ERROR at line 7:

ORA-01843: not a valid month

可以看到to_char(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),'YYYYMMDD')

是取得本日期所在月的第一天，比如SALEDATE是20070312，则这里得到是20070301；

to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1),'YYYYMMDD')是得到本日期下月第一天，是20070401，那么在这两个日期之间的就应该是合法日期了。

但是上面却报月份错误，于是用下面SQL判断月份到底有没问题

• SQL3

select distinct(to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1),'YYYYMMDD')) from TM_WWM

where saledate is not null

and substr(saledate,1,4) between 1970 and 2008

and substr(saledate,5,2) between '01' and '12'

这个SQL没问题，说明1970年到2008年之间所有年月都没问题，可以正常格式化成日期，那就只能说明是DAY有问题了。

• SQL4

select count(*) from

(select * from TM_WWM

where saledate is not null

and substr(saledate,1,4) between 1970 and 2008

and substr(saledate,5,2) between '01' and '12')

where SALEDATE

between to_char(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),'YYYYMMDD')

AND to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1),'YYYYMMDD')

1981098 行记录

注意SQL2和SQL4的区别，SQL2 是年月日的限制都在同一级，那么SQL是按照从右到左去分析执行（以前一直认为不是所有的RBO都是从右到左，所以一直都不在意，今天吃到苦头了，你也可以把先后顺序颠倒下，效果和SQL4一样），SQL4则年月在内侧，在符合年月的情况下“日期“都在本月1号到下月1号之间。到这里就能说SELECT的字符型日期都满足日期格式吗？

• SQL4

select count(to_date(SALEDATE,'YYYYMMDD')) from (select * from (select * from TM_WWM

where saledate is not null

and substr(saledate,1,4) between 1970 and 2008

and substr(saledate,5,2) between '01' and '12')

where SALEDATE

between to_char(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),'YYYYMMDD')

AND to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1),'YYYYMMDD'))

却不行

ERROR at line 1:

ORA-01839: date not valid for month specified

看到将SQL4的COUNT（*）改成count(to_date(SALEDATE,'YYYYMMDD'))，也就是说to_char(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),'YYYYMMDD')可以执行，但是to_date(SALEDATE,'YYYYMMDD')却不可以执行。仔细观察这两个语句是to_date(SUBSTR(SALEDATE,1,6),'YYYYMM')和to_date(SALEDATE,'YYYYMMDD')的区别，还是前面判断的是日的问题而不是月的问题（不知为何ORACLE总体是month有问题）。也就是说如果SALEDATE是20060230,那么between to_char(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),'YYYYMMDD')

AND to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1),'YYYYMMDD'))的范围是20060201和20060301，而字符型20060230也在这个范围内，所以也符合条件能被选出，而to_date(SALEDATE,'YYYYMMDD')的时候却非法了。当时之所以考虑用20060201和20060301范围是因为考虑到时分秒的情况，而本例却没有时分秒，所以将范围改为

• SQL5

select * from (select * from TM_WWM

where saledate is not null

and substr(saledate,1,4) between 1970 and 2008

and substr(saledate,5,2) between '01' and '12' )

where SALEDATE

between to_char(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),'YYYYMMDD')

AND to_char(ADD_MONTHS(to_date(SUBSTR(SALEDATE,1,6),'YYYYMM'),1)-1,'YYYYMMDD')

这个时候范围是20060201和20060228，运行正常。

本例判断字符型日期是否是合法的日期格式采用自己和自己比较的方式一个SQL实现，比较灵活，可移植性也很高。