InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:40:26 GMT20http://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxI got it!I made it too complex. It shoudl be just use :mydate instead of @mydate. Only drackback is the user has to put in the correct date format --- DD-Mon-YYYYThanks you all!Fri, 27 Jan 2012 13:51:47 GMTSeattlemsphttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxPaul ---Thanks a lot for contiuning help me.Your code works in the SQLPlus. How can I convert it into BIDS(SSRS) format, so I can create a report in BIDS(stripdown Microsoft Visual Studio). Then the user can key in the date when run the report via SSRS.Fri, 27 Jan 2012 10:32:05 GMTSeattlemsphttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxPaul ---Thanks a lot for contiuning help me.Your code works in the SQLPlus. How can I convert it into BIDS(SSRS) format, so I can create a report in BIDS(stripdown Microsoft Visual Studio). Then the user can key in the date when run the report via SSRS.Fri, 27 Jan 2012 10:26:44 GMTSeattlemsphttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspx[quote][b]Seattlemsp (1/25/2012)[/b][hr]during the passed days, I tried many times. However it still doesn't work for me.The error message said: ORA-00900: invalid SQL statementIt looks like I may not declare the variable or parameter right, or may not put them in the right place.Such as can I mix the SQL statement with all other inside the qurey build window or not?[/quote]Hard to tell if you do not show us what you are doing but for sure something is wrong with the syntax, check below:[code="sql"]SQL> SQL> variable startdate varchar2(11)SQL> exec :startdate := '01-JAN-2011'PL/SQL procedure successfully completed. SQL> variable enddate varchar2(11)SQL> exec :enddate := '31-DEC-2011'PL/SQL procedure successfully completed. SQL> select created from v$database where created between to_date(:startdate, 'dd-mon-yyyy') and to_date(:enddate, 'dd-mon-yyyy') ;CREATED------------------11-AUG-11 SQL>[/code]Thu, 26 Jan 2012 06:03:48 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxduring the passed days, I tried many times. However it still doesn't work for me.The error message said: ORA-00900: invalid SQL statementIt looks like I may not declare the variable or parameter right, or may not put them in the right place.Such as can I mix the SQL statement with all other inside the qurey build window or not?Thanks!Wed, 25 Jan 2012 22:30:20 GMTSeattlemsphttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxI'm assuming the target database is Oracle and that the datatype of mytable.date is DATE.If this is the case you have two options, option #1 is to write a stored procedure that accepts the two parameters; option #2 is to send the parameters alongside your query which arguable defeats the purpose(*).Having said that, code below shows how to use bind-variables in your Oracle sql code.[code="plain"]VARIABLE STARTDATE VARCHAR2(11)EXEC :STARTDATE := '01-JAN-2012'VARIABLE ENDDATE VARCHAR2(11)EXEC :ENDDATE := '10-JAN-2012'SELECT *FROM MYTABLEWHERE MYTABLE.DATE BETWEEN TO_DATE(:STARTDATE, 'dd-mon-yyyy') AND TO_DATE(:ENDDATE, 'dd-mon-yyyy');[/code]Hope this helps.(*) exception would be a really well organized environment where no literals are allowed on queries.Wed, 11 Jan 2012 14:49:46 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxWhere should I put below code:DECLAREparam_bdate DATE;param_edate DATE;SET param_bdate = TO_DATE('01/15/2010','MM/DD/YYYY');SET param_edate = TO_DATE('01/15/2010','MM/DD/YYYY');When I put above in the Query text area. The error message below:Could not update a list of fields for the query. Verify that you can connect to the dat source and that your query syntax is correct.ORA-06550: line1, colum 8:PLS-00103: Encountered the symbol "" whenexpecting ofe of the following:begin function package pragma procedure subteype type use <an identifier> <a dobule-quoted delimited-identifier> form current cursor.Thanks a lotWed, 11 Jan 2012 14:05:37 GMTSeattlemsphttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxwhen i said parameters, i mean on the report side, sorry for the confusion.if you are building a string and executing it from your report, without parameters, you need to follow the full oracle syntax and rules...oracle doesn't allow variables with @ in them.I don't have oracle handy right now, but here's the basic syntax, I believe.the param_ replacing @ is not required, iot's just a naming convention and is completely optional, i just used it for clarity.[code]DECLAREparam_bdate DATE;param_edate DATE;SET param_bdate = TO_DATE('01/15/2010','MM/DD/YYYY');SET param_edate = TO_DATE('01/15/2010','MM/DD/YYYY');Select * from mytableWhere mytable.date Between param_bdate AND param_edate ;[/code]Wed, 11 Jan 2012 05:22:24 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxThanks your replying.Follow your syntax, I received error message:"An error occurred during local report processing,An error has occurred during report processing,Query execution failed for dataset "DataSet1"ORA-01858: a non-numeric character was found where a numeric was expected"The parameter type either as datetime or text(string) doesn't make difference.By the way, I only used one @in front of enddateWhat I may missed?Wed, 11 Jan 2012 00:20:31 GMTSeattlemsphttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxyou parameters are datetime in the report, right, so they will convert cleanly to the DATE datatype for oracle?It sounds like you are using varchars instead...if your parameters are datetime, that should immediately address the issue, i believe.if you are building a string for the SQL statement to be executed, then use the built in TO_DATE function, and tell it the format of the string.[code]Select * from mytableWhere mytable.date Between TO_DATE(@startdate,'MM-DD-YYYY') And TO_DATE(@@enddate,'MM-DD-YYYY')[/code]Tue, 10 Jan 2012 20:39:28 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1233620-1044-1.aspxCreating a report with Oracle 9i backend database.Setup two parameters@startdate@enddateSelect * from mytableWhere mytable.date Between @startdate And @enddateI received error message bacause oracle requriment date form as 'DD-MOn-YYYY', such as '10-Jan-2012'I test the below qury, it works in the report:Select * from mytableWhere mytable.date Between '1-Jan-2012' And '10-Jan-2012'What the sql should be to conver @startdate to 'DD-MOn-YYYY'?I found a qury which can convert date to the correct format. SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-'). However it doesn't work me enven it works as stand alone qury.So, What correct sql should be to conver @startdate to 'DD-MOn-YYYY' for my report needs?Thanks a lot!Tue, 10 Jan 2012 16:02:19 GMTSeattlemsp