0

I have a table which contains a column of type TIMESTAMP(6), if I try to query this table everything is fine as long as I don't parametrize it.

This works:

SELECT
  COUNT(*)
FROM
  t_data
WHERE
  DATA_TS >= TO_TIMESTAMP('13.09.18 11:30:00')
  AND   DATA_TS <= TO_TIMESTAMP('13.09.18 11:33:00');

However this fails with ORA-01858 "a non-numeric character was found where a numeric was expected" supplying the values as stated above. I also tried to wrap the parameters in TO_TIMESTAMP() using the format Mask from the NLS_PARAMETERS.

SELECT
  COUNT(*)
FROM
  t_data
WHERE
  DATA_TS >= TO_TIMESTAMP(:AStart,'DD.MM.RR HH24:MI:SSXFF')
  AND   DATA_TS <= TO_TIMESTAMP(:AEnd,'DD.MM.RR HH24:MI:SSXFF');

Anyone knows what causes this issue?

Testcase: Create Table

create table t_data ( 
    data varchar2(80), 
    data_ts timestamp 
);

Insert DATA

Insert into t_data (data,data_ts) VALUES ('TEST', systimestamp);

Using &AStart instead of :AStart works well, if that helps somebody

Improve this question
9
  • 1
    What data type is :AStart and :AEnd? Commented Sep 18, 2018 at 7:38
  • I supplied :AStart with TO_TIMESTAMP('13.09.18 11:30:00') Commented Sep 18, 2018 at 7:41
  • 1
    Never use to_timestamp() without a format mask. Use TO_TIMESTAMP('13.09.18 11:30:00', 'dd.mm.rr hh24:mi:ss') Commented Sep 18, 2018 at 7:42
  • THanks, I'll keep that in mind, however this does not solve the issue Commented Sep 18, 2018 at 7:54
  • I wouldn't be surprised if the lack of a format mask is the reason for your error. Commented Sep 18, 2018 at 7:55

4 Answers 4

Reset to default
2

You cannot use TO_TIMESTAMP('13.09.18 11:30:00') as a bind variable content. The DBMS expects a value, not code.

So you'd either pass a timestamp or date value, if your tool or programming language allows that, or you pass the string '13.09.18 11:30:00' only and have TO_DATE(:AStart, 'dd.mm.rr hh24:mi:ss') in your query.

UPDATE: Make sure not to include the quotes when passing a string as a bind variable. Pass the string content only (e.g. 13.09.18 11:30:00, not '13.09.18 11:30:00').

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

See my edit, I tried that. Same issue, it works as long as I dont replace '13.09.18 11:33:00' with a parameter.
So you actually passed a string matching the format 'DD.MM.RR HH24:MI:SSXFF', e.g. '13.09.18 11:30:00.00'? I.e. you added the fractions mentioned in the format string? And this still fails with ORA-01858 "a non-numeric character was found where a numeric was expected"? And you also tried TO_DATE instead of TO_TIMESTAMP as I recommended for your original date string, and this too fails?
That's correct, I tried those combinations already. It also fails if I use this DATA_TS <= :AEnd And supply SYSTIMESTAMP as Value
SYSTIMESTAMP is not a value; it's a function giving you the current time. So this again is code you cannot pass as a bind variable. How are you passing the value? Are you using a programming language or a tool?
I am using the SQL Developer for this. I know that SYSTIMESTAMP is a function, but I did expect to work in this case.
|
1

You should use param inside the function 

SELECT   COUNT(*)
FROM  t_data
WHERE   DATA_TS >= TO_TIMESTAMP(:AStart)
  AND   DATA_TS <= TO_TIMESTAMP(:AEnd);
Improve this answer

1 Comment

then you param is not valid timestimp check for the real input values
1

Is it really a timestamp? Because, values you posted look like ordinary dates to me.

Anyway: I believe you should use TO_TIMESTAMP and provide appropriate format mask to the parameter value. It is not that you should apply NLS settings format mask, but the one you used when you typed value of that parameter.

For example:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
18.09.2018 10:07:57

SQL> select to_timestamp('&par_ts', 'dd.mm.yy hh24:mi:ss') result from dual;
Enter value for par_ts: 18.09.18 10:08:23

RESULT
---------------------------------------------------------------------------
18.09.18 10:08:23,000000000

SQL>

If you passed differently formatted parameter, format mask has to reflect that change:

SQL> select to_timestamp('&par_ts', 'mm-yyyy-dd hh24:mi:ss') result from dual;
Enter value for par_ts: 09-2018-18 10:10:15

RESULT
---------------------------------------------------------------------------
18.09.18 10:10:15,000000000

SQL>

It means that your query should look like this:

SELECT COUNT(*)
FROM  t_data
WHERE DATA_TS >= TO_TIMESTAMP(:AStart, 'dd.mm.yy hh24:mi:ss')
  AND DATA_TS <= TO_TIMESTAMP(:AEnd, 'dd.mm.yy hh24:mi:ss');

Fix the format mask, if necessary.

Improve this answer

2 Comments

You are right, the values I provided are looking like ordinary dates. However even copying an existant value from my table and supplying it to the parameter does not work: VALUE: '13.09.18 11:32:43,000000000' ... DATA_TS >= TO_TIMESTAMP(:AStart,'DD.MM.RR HH24:MI:SSXFF') ...
Consider providing a test case - CREATE TABLE and INSERT INTO sample data, as well as SELECT you then used in order to fetch data from that table. It is difficult to guess what exactly you have, what you did and how Oracle responded.
1

Thanks everyone for your contribution. I was on the wrong track the whole time.

After uninstalling my old version of SQL Developer and installing a current one the issue disappeared. I don't know what caused it but it got fixed with that.

Result of this is I now know more about time and date formatting as before.

Regards Attix

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.