Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> REPOST: Re: how to use bind value in date type?

REPOST: Re: how to use bind value in date type?

From: Mike Liu <mike2322_at_hotmail.com>
Date: 25 Jan 2002 13:28:31 -0800
Message-ID: <7$--$%%%%--$-$$_%$@news.noc.cabal.int>


>
> "Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message
> news:l.1011974823.1024353027@[64.94.198.252]...
> > i am learning how to use bind value. I have a test table with with
> > only two rows
> > Name Null? Type
> > ------ -------- ------
> > COL1 NUMBER
> > COL2 DATE
> >
> > SQL> variable x number
> > SQL> begin
> > 2 :x:=1;
> > 3 end;
> > 4 /
> >
> > PL/SQL procedure successfully completed.
> > SQL> select * from test where col1 = :x;
> >
> > COL1 COL2
> > ---------- ---------
> > 1 24-JAN-02
> >
> > 1 row selected.
> >
> > But I do not know how to use bind value with date type
> >
> > SQL> variable x date
> > Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n) | VARCHAR2(n) |
> > NCHAR | NCHAR (n) | NVARCHAR2 (n) |
> > CLOB | NCLOB | REFCURSOR ] ]
> >
> > SQL> variable x varchar2(60);
> > SQL> begin
> > 2 :x:=to_date('2002/01/24 23:59:59','yyyy/mm/dd hh24:mi:ss');
> > 3 end;
> > 4 /
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL> select * from test where col2=:x;
> > no rows selected
> >
> > but there are two rows in the table
> > SQL> select col1,to_char(col2,'yyyy/mm/dd hh24:mi:ss') from test;
> >
> > COL1 TO_CHAR(COL2,'YYYY/
> > ---------- -------------------
> > 1 2002/01/24 23:59:59
> > 2 2002/01/25 23:59:59
> >
> > 2 rows selected.
> >
> > What i did was wrong?
> >
> > Thanks for you help
> >
> >

The problem is on the variable x. Because it's a varchar2 type variable, when you assign a date value to it, it's converted to varchar2 type using the default format mask. When you use the bind variable in your query, there is another implicit type conversion. You need to set the nls_date_format to include the time part of a date like in the following example.

SQL> drop table t;

Table dropped.

SQL> create table t(f date);

Table created.

SQL> insert into t select sysdate from dual;

1 row created.

SQL> commit
  2 ;

Commit complete.

SQL> select to_char(f, 'YYYY/MM/DD HH24:MI:SS') from t;

TO_CHAR(F,'YYYY/MM/



2002/01/25 13:43:19

SQL> var x varchar2(60)

SQL> alter session set nls_date_format = 'DD-MON-YY';

Session altered.

SQL> exec :x := to_date('2002/01/25 13:43:19', 'YYYY/MM/DD HH24:MI:SS')

PL/SQL procedure successfully completed.

SQL> print x

X



25-JAN-02 SQL> select count(*) from t
  2 where f = :x;

  COUNT(*)


         0

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> exec :x := to_date('2002/01/25 13:43:19', 'YYYY/MM/DD HH24:MI:SS')

PL/SQL procedure successfully completed.

SQL> print x

X



2002/01/25 13:43:19

SQL> select count(*) from t
  2 where f = :x;

  COUNT(*)


         1

HTH,
Mike

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 15:28:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US