出现2和小数,是应该你显示的数据精度的问题。 你试试 SQL>select mod(1.2, 2) test from dual;SQL>select mod(1.99999995, 2) test from dual;SQL>select mod(1.99999993, 2) test from dual;SQL>col test col 9.99999999;在来看看上面的结果然后 SQL>col test col 9.99;再来看看结果。
嘿嘿,不明白大侠的那句是什么意思,不过还是不行.. SQL> col test col 9.99999999; SQL> select mod(1.99999993, 2) test from dual; TEST ---------- 1.99999993SQL> col test col 9.99; SQL> select mod(1.99999993, 2) test from dual; TEST ---------- 1.99999993
这样吧,你执行这个查询 select mod(1.9999999999999993, 2) test from dual; inthirties想表达的就是这个意思
是的,试试这个句子,不是mod本身的问题。而是数据精度,长度和现实格式上的综合问题。
哦,谢谢各位大侠执行widewave的: SQL> select mod(1.9999999999999993, 2) test from dual; TEST ---------- 2但是:SQL> select mod(1.99999993, 2) test from dual; TEST ---------- 1.99999993我现在该怎么做才能得到我想要的结果呢?
... 你想要的是什么结果? select mod(1.99999993, 2) test from dual; 这里1.99999993没达到系统默认进位的长度,不会四舍五入成2
这样试试你看对吗 select mod(minus_time,2) from ( select extract(day from ti)*24*3600+ extract(hour from ti)*3600+extract(minute from ti)*60+ extract(second from ti) minus_time from (select sysdate-to_timestamp('20090903 08:59:01','yyyymmdd hh24:mi:ss') ti from dual)) /
再想了一下,问题可能是由于sysdate返回的是一个带毫秒的时间,这样相减以后就出现了小数....
-------------------------------------------------------------------------------
636262OPER@tl> /TO_NUMBER(SYSDATE-TO_DATE('2009-09-0308:59:01','YYYY-MM-DDHH24:MI:SS'))*3600*24
-------------------------------------------------------------------------------
636264OPER@tl> /TO_NUMBER(SYSDATE-TO_DATE('2009-09-0308:59:01','YYYY-MM-DDHH24:MI:SS'))*3600*24
-------------------------------------------------------------------------------
636265OPER@tl> /TO_NUMBER(SYSDATE-TO_DATE('2009-09-0308:59:01','YYYY-MM-DDHH24:MI:SS'))*3600*24
-------------------------------------------------------------------------------
636265OPER@tl> select mod(to_number(sysdate-to_date('2009-09-03 08:59:01','yyyy-mm-dd hh24:mi:ss'))*3600*24,2) from dual;MOD(TO_NUMBER(SYSDATE-TO_DATE('2009-09-0308:59:01','YYYY-MM-DDHH24:MI:SS'))*3600*24,2)
--------------------------------------------------------------------------------------
2OPER@tl> /MOD(TO_NUMBER(SYSDATE-TO_DATE('2009-09-0308:59:01','YYYY-MM-DDHH24:MI:SS'))*3600*24,2)
--------------------------------------------------------------------------------------
8.0000E-34OPER@tl> /MOD(TO_NUMBER(SYSDATE-TO_DATE('2009-09-0308:59:01','YYYY-MM-DDHH24:MI:SS'))*3600*24,2)
--------------------------------------------------------------------------------------
1OPER@tl> /MOD(TO_NUMBER(SYSDATE-TO_DATE('2009-09-0308:59:01','YYYY-MM-DDHH24:MI:SS'))*3600*24,2)
--------------------------------------------------------------------------------------
0
你查询的结果mod后最大的是2,应该是四舍五入的结果
from dual;
在我这还不ok
select mod(trunc(to_number(sysdate-to_date('2009-09-03 08:59:01','yyyy-mm-dd hh24:mi:ss'))*3600*24),2) , sysdate from dual;
---------- -------------------
0 2009-09-10 20:49:11SQL> select mod(trunc(to_number(sysdate-to_date('2009-09-03 08:59:01','yyyy-mm-dd hh24:mi:ss'))*3600*24),2) rs, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd from dual; RS SD
---------- -------------------
0 2009-09-10 20:49:12SQL> select mod(trunc(to_number(sysdate-to_date('2009-09-03 08:59:01','yyyy-mm-dd hh24:mi:ss'))*3600*24),2) rs, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd from dual; RS SD
---------- -------------------
0 2009-09-10 20:49:13SQL> select mod(trunc(to_number(sysdate-to_date('2009-09-03 08:59:01','yyyy-mm-dd hh24:mi:ss'))*3600*24),2) rs, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd from dual; RS SD
---------- -------------------
1 2009-09-10 20:49:14不行...不好意思,回复慢了点
trunc了以后,只剩下0和1两种结果了,正常啊
像我贴出的中:
如果 sysdate=2009-09-10 20:49:11时mod后的结果为0,
那么 sysdate=2009-09-10 20:49:12时mod后的结果应该为1,
可是上面的却是0....
可惜oracle的时间戳不像是java或unix的时间戳似的能返回一个毫秒级的number...
2 (to_date('2009-09-03 08:59:01',
3 'yyyy-mm-dd hh24:mi:ss')-TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)),
4 2) rs,
5 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd
6 from dual; RS SD
---------- -------------------
1 2009-09-10 21:42:32SQL>
SQL> select mod(trunc(to_number((sysdate -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 -
2 (to_date('2009-09-03 08:59:01',
3 'yyyy-mm-dd hh24:mi:ss')-TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)),
4 2) rs,
5 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd
6 from dual; RS SD
---------- -------------------
0 2009-09-10 21:42:33SQL>
SQL> select mod(trunc(to_number((sysdate -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 -
2 (to_date('2009-09-03 08:59:01',
3 'yyyy-mm-dd hh24:mi:ss')-TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)),
4 2) rs,
5 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd
6 from dual; RS SD
---------- -------------------
1 2009-09-10 21:42:34SQL>
SQL> select mod(trunc(to_number((sysdate -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 -
2 (to_date('2009-09-03 08:59:01',
3 'yyyy-mm-dd hh24:mi:ss')-TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)),
4 2) rs,
5 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd
6 from dual; RS SD
---------- -------------------
0 2009-09-10 21:42:35SQL>
SQL> select mod(trunc(to_number((sysdate -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 -
2 (to_date('2009-09-03 08:59:01',
3 'yyyy-mm-dd hh24:mi:ss')-TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)),
4 2) rs,
5 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd
6 from dual; RS SD
---------- -------------------
1 2009-09-10 21:42:36SQL>
SQL> select mod(trunc(to_number((sysdate -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 -
2 (to_date('2009-09-03 08:59:01',
3 'yyyy-mm-dd hh24:mi:ss')-TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)),
4 2) rs,
5 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sd
6 from dual; RS SD
---------- -------------------
0 2009-09-10 21:42:37等等..貌似解决了YEAH?
你试试
SQL>select mod(1.2, 2) test from dual;SQL>select mod(1.99999995, 2) test from dual;SQL>select mod(1.99999993, 2) test from dual;SQL>col test col 9.99999999;在来看看上面的结果然后
SQL>col test col 9.99;再来看看结果。
嘿嘿,不明白大侠的那句是什么意思,不过还是不行..
SQL> col test col 9.99999999;
SQL> select mod(1.99999993, 2) test from dual; TEST
----------
1.99999993SQL> col test col 9.99;
SQL> select mod(1.99999993, 2) test from dual; TEST
----------
1.99999993
select mod(1.9999999999999993, 2) test from dual;
inthirties想表达的就是这个意思
是的,试试这个句子,不是mod本身的问题。而是数据精度,长度和现实格式上的综合问题。
哦,谢谢各位大侠执行widewave的:
SQL> select mod(1.9999999999999993, 2) test from dual; TEST
----------
2但是:SQL> select mod(1.99999993, 2) test from dual; TEST
----------
1.99999993我现在该怎么做才能得到我想要的结果呢?
你想要的是什么结果?
select mod(1.99999993, 2) test from dual;
这里1.99999993没达到系统默认进位的长度,不会四舍五入成2
select mod(minus_time,2) from (
select extract(day from ti)*24*3600+
extract(hour from ti)*3600+extract(minute from ti)*60+
extract(second from ti) minus_time from
(select sysdate-to_timestamp('20090903 08:59:01','yyyymmdd hh24:mi:ss') ti
from dual))
/