Re: 6781 DATE Formatting with Oracle Developer

In article <7dd8f7$a2a$1_at_pthp35.telecom.pt>, João Alves da Silva <joao-r-silva_at_telecom.pt> wrote:
>The method of Geoff White works, but I don't understand Why do you want to
>do this.
>If it's because de date comparisation why don't you store only the date
>without the hour ( ex: trunc(sysdate) ). It saves a lot of work.

BE CAREFUL when playing with dates!!!

Geoff's method will NOT work:
  SQL> select to_char(trunc(sysdate)

    2                  + to_date('23:59:59', 'HH24:MI:SS'),
    3                  'MM-DD-YYYY HH24:MI:SS')
    4  from dual;
                  + to_date('23:59:59', 'HH24:MI:SS'),
                  *

Note that to_date('23:59:59' requires the application to come up with a default value for Month, Day, and Year. Also note that the original format given was 'HH:MM:SS'. Can you spot the TWO errors??? It should be HH24, not HH, and MM is NOT minutes, it is months.

The right way to set time of a date to 23:59:59 is: :Block.Date1 := trunc(:Block.Date1) + 86399/86400;

       or
:Block.Date1 := trunc(:Block.Date1) + .99999;

But I also think you are going down the wrong path by setting time to 23:59:59 in your dates. I can't imagine a valid reason to do such a thing.

Steve Cosner Received on Fri Mar 26 1999 - 07:38:03 CET