时间字符串和date类型转化为时间字符串比较

 

时间字符串和date类型转化为时间字符串比较

 

创建测试表如下：

drop table test;

create table test(pkid      number,

                  lasttime  char(14));

 

 

insert into test(PKID,lasttime) values(1,'20070109');                 

insert into test(PKID,lasttime) values(2,'20070309');  

insert into test(PKID,lasttime) values(3,'20070409');  

insert into test(PKID,lasttime) values(4,'20070409');  

insert into test(PKID,lasttime) values(5,'20070409');  

insert into test(PKID,lasttime) values(6,'20070409');  

insert into test(PKID,lasttime) values(7,'20070809');  

insert into test(PKID,lasttime) values(8,'20070909');  

insert into test(PKID,lasttime) values(9,'20071009');  

 

select length(lasttime) from test;

LENGTH(LASTTIME)

----------------

              14

 

今天在工作遇到一个问题，出现时间字段是char(14)类型的比较问题，在数据库中该字段的实际输入值是只有8位，因为是char类型，所以oracle自动为该

字段上每个实际输入8位字符串的值后面补充空值6位直到长度为14位。这样就会出现下面的情况：

 

SQL> select (select count(*) from test a where a.lasttime>='20070409')

  2        ,(select count(*) from test a where a.lasttime>=to_char(to_date('20070409','yyyymmdd'),'yyyymmddhh24miss'))

  3  from dual;

(SELECTCOUNT(*)FROMTESTAWHEREA (SELECTCOUNT(*)FROMTESTAWHEREA

------------------------------ ------------------------------

                             7                              3

                            

编写该语句的人本意是想两个求count的值是应该相等，居然不相等，下面解释原因：

 

SQL> select * from dual where '20070409000000'=to_char(to_date('20070409','yyyymmdd'),'yyyymmddhh24miss');

DUMMY

-----

X

 

SQL> select * from dual where ' '<'0';

DUMMY

-----

X

 

由上面的简单测试可以看出：空格字符是小于'0'字符的，所以在数据库中该char(14)字段中字符值为8位的由于oracle会在其值后面添加6个空格，所以实际的比较是如下：

 

SQL>  select * from dual where '20070409      '=to_char(to_date('20070409','yyyymmdd'),'yyyymmddhh24miss');

 

DUMMY

-----

 

从结果可以看出：没有任何结果输入，说明'20070409      '与to_char(to_date('20070409','yyyymmdd'),'yyyymmddhh24miss')是不等的，也就是

'20070409      '与'20070409000000'是不等的，所以上面的例子中求count的两个值不相等。

 

 

解决办法可以如下：

 SQL> select (select count(*) from test a where a.lasttime>='20070409')

  2 ,(select count(*) from test a where replace(a.lasttime,' ','0')>=to_char(to_date('20070409','yyyymmdd'),'yyyymmddhh24miss'))

  3    from dual;

(SELECTCOUNT(*)FROMTESTAWHEREA (SELECTCOUNT(*)FROMTESTAWHERER

------------------------------ ------------------------------

                             7                              7

将表中的字段值中空格转换为'0'，然后再比较的话可以得到想要的效果，但是最好是在插入操作时传进去的值最好是传和字段定义的类型长度一样(针对char类型)，因为在字段长加replace函数的话会影响索引的使用，影响性能，除非建立函数索引才能使用索引。