| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Time is cut off from sysdate using PL/SQL
Andy is correct, in that the TO_DATE of SYSDATE is implicitly
performing a TO_CHAR of the date, and this conversion will use the
NLS_DATE_FORMAT by default.
The NLS_DATE_FORMAT is set in the database initialization file. You can override it on the client via a Windows registry entry NLS_DATE_FORMAT, or via an ALTER SESSION command when connected to your Oracle database.
In your script, overriding the current NLS_DATE_FORMAT:
DECLARE
date1 DATE;
date2 DATE;
BEGIN
date1 := TO_DATE(SYSDATE, 'DD.MM.RRRR HH24:MI:SS');
SELECT sysdate
INTO date2
FROM dual;
DBMS_OUTPUT.PUT_LINE ('Date1: '||TO_CHAR(date1, 'DD.MM.RRRR
HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE ('Date2: '||TO_CHAR(date2, 'DD.MM.RRRR
HH24:MI:SS'));
end;
/
results in:
Date1: 15.07.1999 08:41:05
Date2: 15.07.1999 08:41:05
PL/SQL procedure successfully completed.
On Thu, 15 Jul 1999 10:26:29 +0100, Andy Hardy <aph_at_ahardy.demon.co.uk> wrote:
>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
>===============================================================
Thanks!
Joel
Joel R. Kallman Oracle Service Industries
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |