Jul 292013
create table of_datetimes (
  just_before_midnight date

insert into of_datetimes 
values (trunc(sysdate)-0.00001);

alter session set 
  nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

select just_before_midnight 
from   of_datetimes;

28-jul-2013 23:59:59      

Typ=12 Len=7: 120,113,7,28,24,60,60

For some reason 23:59:59 appears as 24:00:00 in the dump of the column! Is this a bug or rounding
error? I'm not sure, but I see this in different versions (code above was on

  2 Responses to “The 24 Hour Dump”

  1. Chris,

    I think
    explains that.

    Everything is all right ( except that you missed an “i” in your last query)


    • Well spotted, I’ve updated the query.

      Thanks for the link; it does explain why this happens. I a little surprised to see 24,60,60 when debugging an issue recently; it doesn’t help that the “unstored” select trunc(sysdate)-0.00001 appears as 23,59,59!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>