Home » SQL & PL/SQL » SQL & PL/SQL » Problem with date in bind variable (Oracle 9i)
Problem with date in bind variable [message #366285] Fri, 12 December 2008 11:37 Go to next message
floridagal
Messages: 2
Registered: December 2008
Junior Member
I've boiled it down to this:

variable l_startdate date := TO_DATE('10/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS');

SELECT TIMESTAMP

FROM MYTABLE

WHERE TO_DATE(TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS') >= :L_STARTDATE;

Timestamp is a DATE field.

Error is:

Oracle -01858 A non-numeric character was found where a numeric was expected.



Ideas welcomed
Re: Problem with date in bind variable [message #366290 is a reply to message #366285] Fri, 12 December 2008 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is a DATE field, TO_DATE on a date is meaningless (usually I say silly).

Regards
Michel
Re: Problem with date in bind variable [message #366293 is a reply to message #366290] Fri, 12 December 2008 12:32 Go to previous messageGo to next message
floridagal
Messages: 2
Registered: December 2008
Junior Member
I get the same error without the TO_DATE on the date field. I just thought it may have been a formatting issue.
Re: Problem with date in bind variable [message #366298 is a reply to message #366293] Fri, 12 December 2008 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think we are talking about the same TO_DATE but as you don't post as requested, I can't say.

Regards
Michel

[Updated on: Fri, 12 December 2008 12:49]

Report message to a moderator

Re: Problem with date in bind variable [message #366302 is a reply to message #366285] Fri, 12 December 2008 12:56 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you fake it? If you have executed it in SQL*Plus (did you execute it in SQL*Plus?), you'd run into an error much earlier as VARIABLE doesn't support DATE datatype (see the Oracle 9i VARIABLE reference) and my 10g (as I don't have 9i) example:
SQL> variable l_start date := to_date('10.01.2008', 'dd.mm.yyyy');
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
                    BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL>

So, what did you REALLY do?
Re: Problem with date in bind variable [message #366304 is a reply to message #366302] Fri, 12 December 2008 13:06 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Though ... did you mean to do something like this?
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 TIMESTAMP                                          DATE

SQL> select * From mytable;

TIMESTAMP
----------------
12.12.2008 19:44

SQL> var l_start varchar2(20);
SQL> exec :l_start := '10.01.2008 00:00';

PL/SQL procedure successfully completed.

SQL> select * from mytable
  2  where timestamp >= to_date(:l_start, 'dd.mm.yyyy hh24:mi');

TIMESTAMP
----------------
12.12.2008 19:44

SQL> select * from mytable
  2  where timestamp < to_date(:l_start, 'dd.mm.yyyy hh24:mi');

no rows selected

SQL>
Re: Problem with date in bind variable [message #366306 is a reply to message #366285] Fri, 12 December 2008 13:09 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
And if you would like to understand, why not to use TO_DATE on DATE value, have a look at (at least the end of) this thread: http://www.orafaq.com/forum/m/277319/96705/.
Previous Topic: Get particular data using decode(urgent)
Next Topic: Split Partition with Subpartition
Goto Forum:
  


Current Time: Wed Dec 07 16:47:16 CST 2016

Total time taken to generate the page: 0.05280 seconds