让传进来的参数是日期型,不管是什么的日期型,delphi, vc or any other tools,都可以,但是你得保证是日期。否则,他输乱七八糟的东西你怎么控制?该用 to_char 的地方,用 to_char; 该用 to_date 的地方,用 to_date。to_char(fieldname, 'yyyy-mm-dd hh24:mi:ss') = to_char(paraname, 'yyyy-mm-dd hh24:mi:ss')let me think, maybe ok, maybe not. Not sure. just FYI
paste your report format pls. and the sql statement you used, even failed.
or you can use the "alter session" following the "connect" statement. then use the database field and the parameter directly. I think you don't need to modify the others. The following is the test case in sqlplus, I think it should be the same as in the other development tools.you should use 单引号read the following case carefully, maybe it can be helpful to you. and wish so.SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi;ss';会话已更改。SQL> select * from dual where sysdate = '23'; select * from dual where sysdate = '23' * ERROR 位于第 1 行: ORA-01840: input value not long enough for date formatSQL> select * from dual where sysdate = '2003-06-21 02:06:54';未选定行SQL> alter session set nls_date_format='yyyy.mm.dd';会话已更改。SQL> select * from dual where sysdate = '2003.06.21';未选定行SQL> select * from dual where sysdate = '2003-06-21 02:06:54'; select * from dual where sysdate = '2003-06-21 02:06:54' * ERROR 位于第 1 行: ORA-01830: date format picture ends before converting entire input stringSQL> select * from dual where sysdate = 2003.06.21; select * from dual where sysdate = 2003.06.21 * ERROR 位于第 1 行: ORA-00933: SQL command not properly ended
did you try it? if it could work, you could change your code according to it, I think.if you want the GLOBAL, try add the nls_date_format in your init.ora file, then reboot your database, and test.
getnull 你是怎么理解的?
我的问题是这样的。正常开发下,根本就不用关心oracle的日期存储格式,但是我们用了一个糟糕的报表,他要求输入的参数必须和数据库的实际存储格式一致。我使者把传近来的蚕食和数据库中的数据同to char转换,然后比较,但是报表出错。所以我必须改变oracle的日期存储格式。另外。我和getnull是一个公司的。
我们遇到了这个问题,请oracle高手指教。
该用 to_date 的地方,用 to_date。to_char(fieldname, 'yyyy-mm-dd hh24:mi:ss') = to_char(paraname, 'yyyy-mm-dd hh24:mi:ss')let me think, maybe ok, maybe not. Not sure. just FYI
to_char(fieldname, 'AsYourParaFormatString') = paraname
then use the database field and the parameter directly. I think you don't need to modify the others.
The following is the test case in sqlplus, I think it should be the same as in the other development tools.you should use 单引号read the following case carefully, maybe it can be helpful to you. and wish so.SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi;ss';会话已更改。SQL> select * from dual where sysdate = '23';
select * from dual where sysdate = '23'
*
ERROR 位于第 1 行:
ORA-01840: input value not long enough for date formatSQL> select * from dual where sysdate = '2003-06-21 02:06:54';未选定行SQL> alter session set nls_date_format='yyyy.mm.dd';会话已更改。SQL> select * from dual where sysdate = '2003.06.21';未选定行SQL> select * from dual where sysdate = '2003-06-21 02:06:54';
select * from dual where sysdate = '2003-06-21 02:06:54'
*
ERROR 位于第 1 行:
ORA-01830: date format picture ends before converting entire input stringSQL> select * from dual where sysdate = 2003.06.21;
select * from dual where sysdate = 2003.06.21
*
ERROR 位于第 1 行:
ORA-00933: SQL command not properly ended
if it could work, you could change your code according to it, I think.if you want the GLOBAL, try add the nls_date_format in your init.ora file, then reboot your database, and test.
NLS_DATE_FORMAT =
这个会影响整个数据库,建义通知管理员。在注册表:hkey->software->oracle,找到nls_date_format,设置其值
这个只影响客户端显示
没有可以右击鼠标“增加”一字串,使其值赋为:'yyyy-mm-dd hh24:mi:ss'