Search code examples
oracledatetimeoracle11gdate-arithmetic

Strange results generated for ORACLE sql query

I am having some problem with weblogic ACTIVE table in a Oracle database (Oracle version 11g) During the investigation of that problem, I tried to get the result of following query.

Expected result of following query is, to get a value with current time + 30 seconds as the second field "to_char(sysdate + (30/86400), 'YYYY-MM-DD hh:mm:ss')". But sometimes I get the result otherway (first field is greater than second field by 30 second). After few executions, I get the results correct way again. and after sometime, I get the incorrect result.

select to_char(sysdate, 'YYYY-MM-DD hh:mm:ss'), to_char(sysdate + (30/86400), 'YYYY-MM-DD hh:mm:ss') from dual;

Could any one help me to understand what is the problem here? Is this a bug in Oracle DB?

The incorrect result

The incorrect result:

to_char(sysdate, 'YYYY-MM-DD hh:mm:ss')     to_char(sysdate + (30/86400), 'YYYY-MM-DD hh:mm:ss')  
2013-07-05 10:07:54                     2013-07-05 10:07:24

2013-07-05 10:07:54 2013-07-05 10:07:24

Solution

  • Your format string should be 'YYYY-MM-DD HH:MI:SS'. Your code displays the month, not the minute.