Creating Oracle Date parameter

[Kevin]

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?  
 

[Hadib Ahmabi]

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 ' '.

[Kevin]

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!

[Kevin]

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

[Hadib Ahmabi]

Try with:
new_time(to_date (:startdate, 'mm/dd/yyyy'), 'CST','GMT') 

[Kevin]

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

[Hadib Ahmabi]

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