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 
           looks_different_in_the,
       dump(just_before_midnight) 
           than_you_might_expect
from   of_datetimes;

LOOKS_DIFFERENT_IN_THE 
---------------------- 
28-jul-2013 23:59:59      

THAN_YOU_MIGHT_EXPECT
-----------------------------------
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 11.2.0.2)

  2 Responses to “The 24 Hour Dump”

  1. Chris,

    I think
    http://www.ixora.com.au/notes/date_representation.htm
    explains that.

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

    Matthias

    • 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>

(required)

(required)