Re: SYSDATE truncated (OCI Problem?)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 04 Dec 1998 14:06:30 GMT
Message-ID: <3670ebc7.9910260@192.86.155.100>

A copy of this was sent to Thomas Broquist <thomas.broquist_at_computech.se> (if that email address didn't require changing) On Fri, 04 Dec 1998 09:38:19 +0100, you wrote:

>Hi!
>
>I've got a problem with an UPDATE statement called from OCI:
>
>#define ORA_MOD "UPDATE turn SET been_there = DECODE(:1, 0, NULL,
>SYSDATE) WHERE login = :2"

its not OCI, its the sql.

the problem is that decode( :1, 0, NULL, ... ) implicity makes the decode return a CHARACTER string (null is typeless, decode is assuming char). that means your sysdate is being converted into a character string using your nls_date_format (probably no time portion in that) and then back into a date after the update.

The following example shows your decode in action (losing the time component) and a 'fixed' decode that saves the time portion:

SQL> create table dates ( msg varchar2(20), d date ); Table created.

SQL> variable x number
SQL> exec :x := 0;
PL/SQL procedure successfully completed.

SQL> insert into dates
2 select 'Null One', decode( :x, 0, NULL, SYSDATE ) from dual; 1 row created.

SQL> insert into dates
2 select 'non null', decode( :x, 1, NULL, SYSDATE ) from dual; 1 row created.

SQL> insert into dates
2* select 'with to_date', decode( :x, 1, to_date(null), sysdate ) from dual; 1 row created.

SQL> select msg, to_char( d , 'dd-mon-yyyy hh24:mi:ss' ) from dates;

MSG                  TO_CHAR(D,'DD-MON-YY
-------------------- --------------------
Null One
non null             04-dec-1998 00:00:00
with to_date         04-dec-1998 09:00:42

so, by casting the NULL with a to_date -- it works, decode returns a date, not a char

>/* [snip] */
>oopen(&cda_mod, &lda, 0, -1, -1, 0, -1);
>oparse(&cda_mod, ORA_MOD, -1, 0, 2) != 0);
>obndrn(&cda_mod, 1,(text*)&been_there, sizeof(been_there), SQLT_INT, -1,
>0, 0, -1, -1);
>obndrn(&cda_mod, 2,(text*)login, sizeof(login), SQLT_STR, -1, 0, 0, -1,
>-1);
>been_there = 1;
>oexec(&cda_mod);
>ocom(&lda);
>/* [snip] */
>
>When executed, this statement updates the been_there DATE column of the
>turn table to
>SYSDATE (when been_there != 0). However, the resulting value is
>truncated to the
>beginning of the day.
>When i run the above statement in SQL*plus it works just fine.
>
>Platform:
>HP-UX
>
>Environment (among others):
>NLS_LANG=american_america.we8iso8859p1
>ORACLE_SID=oradb6
>ORA_NLS32=/oracle_sw/donald/oracle732/ocommon/nls/admin/data
>
>Database:
>Oracle7 Server Release 7.3.2.3.0
>
>What am I missing? An ALTER SESSION SET NLS_XXX or something like that?
>
>Any hints would be greatly apreciated!
>
>Regards Thomas Broquist

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Dec 04 1998 - 08:06:30 CST