This is a migrated thread and some comments may be shown as answers.

Creating Oracle Date parameter

6 Answers 76 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Kevin
Top achievements
Rank 1
Kevin asked on 26 Mar 2012, 10:29 PM
Hi.  I am running Windows 7 32 bit, using Telerik Report Q3 2011 in Visual Studios 2010.  I am new to Telerik.  I have succeeded in creating my connection to our Oracle database, but have not been able to figure out the correct syntax for a Date parameter.

My query works if I use
where a.open_time > to_date ('03/23/2012 00:00:01', 'mm/dd/yyyy HH24:MI:SS')

I am trying to set my paramater as a DATE.  I have tried 
where a.open_time > to_date ('?Parameter1 00:00:01', 'mm/dd/yyyy HH24:MI:SS')
 and
where a.open_time > to_date ('?Parameter1, 00:00:01', 'mm/dd/yyyy HH24:MI:SS')
and
where a.open_time > to_date ('"?Parameter1" 00:00:01', 'mm/dd/yyyy HH24:MI:SS')
All of these return "a non-numeric character was found where a numeric was expected.

Obviously I don't understand the correct syntax.  Can someone tell me how to properly create a Date parameter to be used against an Oracle database?  
 

6 Answers, 1 is accepted

Sort by
0
Hadib Ahmabi
Top achievements
Rank 1
answered on 29 Mar 2012, 10:25 AM
I have not worked with Oracle but following common logic, you do not need parenthesis around the parameter because this way it is read as a string. Also aren't the Oraclse parameters with ':', instead of '?'. I believe your query should look like this:

where a.open_time > :Parameter1    

(Also if you set the parameter type to date, you don;t need the conversion, unless it is a string)
If it is a string, still it should look something like this:

where a.open_time > to_date (:Parameter1, 'mm/dd/yyyy HH24:MI:SS') without any ' '.
0
Kevin
Top achievements
Rank 1
answered on 29 Mar 2012, 08:41 PM
Hadib,

Thanks for your reply.  You're correct, I should have been using ':' instead of '?'.   The correct syntax for what I needed is
where a.open_time > NEW_TIME(:startdate,'CST' ,'GMT')

Thanks again for responding!
0
Kevin
Top achievements
Rank 1
answered on 17 May 2012, 07:27 PM
Hiya.  I have a follow up now.  The following works as a date parameter:
where a.open_time > NEW_TIME(:startdate,'CST' ,'GMT')

I am trying to make it work using TO_DATE in conjunction with new time, but am unable to figure out the syntax.  How do I replace the date in the following string with a date parameter?

new_time(to_date ('5/16/2012', 'mm/dd/yyyy'), 'CST','GMT')

Thanks!

Kevin
0
Hadib Ahmabi
Top achievements
Rank 1
answered on 19 May 2012, 12:44 PM
Try with:
new_time(to_date (:startdate, 'mm/dd/yyyy'), 'CST','GMT') 

0
Kevin
Top achievements
Rank 1
answered on 21 May 2012, 07:51 PM
Hadib,

Thanks for your reply.  Unfortunately this
NEW_TIME(to_date(:startdate,'mm/dd/yyyy'),'CST','GMT')

returns "a non-numeric character was found where a numeric was expected."

Any other ideas?

Thanks!

Kevin
0
Hadib Ahmabi
Top achievements
Rank 1
answered on 22 May 2012, 09:21 AM
Your input data is in the wrong format. When you type to_date(:date, 'mm/dd/yyyy') the function expects exactly the same format. If it is not you will get an error. You can try without the format string - simply to_date(:date). 
see these articles: 
http://www.techonthenet.com/oracle/functions/to_date.php 
http://www.orafaq.com/forum/t/45456/2/ 
http://www.dbforums.com/oracle/993718-ora-01858-non-numeric-character-found-where-numeric-expected.html 

Tags
General Discussions
Asked by
Kevin
Top achievements
Rank 1
Answers by
Hadib Ahmabi
Top achievements
Rank 1
Kevin
Top achievements
Rank 1
Share this question
or