Re: Time is cut off from sysdate using PL/SQL

In article <7mk59i$a08$1_at_nnrp1.deja.com>, Peter C. <pcugier_at_my-deja.com> writes
>I discovered that the time part of sysdate is cut of using sysdate in a
>to_date function in a PL/SQL procedure.
>If you first select sysdate into a local variable it doesn't happen.
>Is this a normal behaviour of PL/SQL or more like a bug?
>
>Here's a sample program which explains what I mean.
>
>SQL> DECLARE
> 2 date1 DATE;
> 3 date2 DATE;
> 4 BEGIN
> 5 date1 := TO_DATE(SYSDATE, 'DD.MM.RRRR HH24:MI:SS');

When I run this I get a:

ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 5

However, if I do:

  5 date1 := TO_DATE(to_char(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS'); I get:

Date1: 15.07.1999 10:13:19
Date2: 15.07.1999 10:13:19

If I use:

5 date1 := to_date(sysdate, 'DD-MON-YY');

I get the truncated date1 returned.

DD-MON-YY is the way in which dates are returned to the console on my system. I'd guess that line 5 is implicitly returning the sysdate in this way (casting to a char), before it is recast as a date.

So I think that it is something to do with the way in which your system returns dates to consoles. I'm not sure where this is set. My nls_date_format is DD/MM/YYYY HH24:MI:SS and all other parameters are AMERICAN. What OS is this on?

Andy
--
Andy Hardy. PGP key available on request