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

  有一用户的表需要导入到我们系统中,原表TM_WWM中日期是VARCHAR8)型,格式为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天,或230日之类的。一开始想做一个日期表,存放几十年没月的开始日期结束日期,然后把这两个表中日期字段进行比较来判断是否合法。再或者不用中间表,用小时候学到的判断大小月的方法,1357810,腊,这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')
是取得本日期所在月的第一天,比如 SALEDATE20070312,则这里得到是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 行记录
注意 SQL2SQL4的区别,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
       看到将SQL4COUNT*)改成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有问题)。也就是说如果SALEDATE20060230,那么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'))的范围是2006020120060301,而字符型20060230也在这个范围内,所以也符合条件能被选出,而to_date(SALEDATE,'YYYYMMDD')的时候却非法了。当时之所以考虑用2006020120060301范围是因为考虑到时分秒的情况,而本例却没有时分秒,所以将范围改为
  • 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')
       这个时候范围是2006020120060228,运行正常。
       本例判断字符型日期是否是合法的日期格式采用自己和自己比较的方式一个SQL实现,比较灵活,可移植性也很高。
 
 
 
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值