Re: how to use bind value in date type?

From: Fernão Magalhaes <barameda_at_no.spam.hotmail.com>
Date: Fri, 25 Jan 2002 22:07:58 -0800
Message-ID: <uEAyX$ipBHA.1192@cpimsnntpa03>

You can also try including the conversion function within the SELECT statement:

SQL> VARIABLE x VARCHAR2(60);
SQL> EXEC :x := '2002/01/24 23:59:59';

PL/SQL procedure successfully completed.

SQL> SELECT *
  2 FROM test
  3 WHERE :x = TO_CHAR(col2, 'yyyy/mm/dd hh24:mi:ss');

      COL1 COL2
---------- ---------

         1 24-JAN-02 --Fernão

"Mike Liu" <mike2322_at_hotmail.com> wrote in message news:2262aa8e.0201251328.49f21ff2_at_posting.google.com...
>
> "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 Received on Sat Jan 26 2002 - 00:07:58 CST