|
|
You're in the
sql server reporting services
group:Help with parameter
sql server reporting services:
I am querying a sybase database for my table & need to add a start date & end date to the query. my where statement looks like this WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) can i please have some assistance around inserting the parameters start_date & end_date i have tried WHERE (ACDCallDetail.CallStartDt>=:start_date AND ACDCallDetail.CallStartDt<:end_date) and also WHERE (ACDCallDetail.CallStartDt>=@start_date AND ACDCallDetail.CallStartDt<@end_date) to no avaial. i also need to have the query that extracts the month only (for a month to date) out of say the start_date & the end_date (for month to date figures) Thankyou in adavance for any help
What happens when you use
WHERE ACDCallDetail.CallStartDt >= @start_date AND ACDCallDetail.CallStartDt < @end_date ? Derrick [quoted text, click to view] "Tango" <Tango@discussions.microsoft.com> wrote in message news:3401F98B-707F-4FA2-BEB7-33699994222D@microsoft.com... > Hi, > I am querying a sybase database for my table & need to add a start date & > end date to the query. > my where statement looks like this > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) > > can i please have some assistance around inserting the parameters > start_date > & end_date > > i have tried > > WHERE (ACDCallDetail.CallStartDt>=:start_date AND > ACDCallDetail.CallStartDt<:end_date) and also WHERE > (ACDCallDetail.CallStartDt>=@start_date AND > ACDCallDetail.CallStartDt<@end_date) to no avaial. > > i also need to have the query that extracts the month only (for a month to > date) out of say the start_date & the end_date (for month to date figures) > > Thankyou in adavance for any help > > todd
The parameters box comes up & i enter dates (tried both 01/08/2005 or
01/08/2005 12:00 AM) & i get following error message must declare variable '@start_date' Thanks Todd [quoted text, click to view] "Derrick Van Hoeter" wrote:
> What happens when you use > > WHERE ACDCallDetail.CallStartDt >= @start_date > AND ACDCallDetail.CallStartDt < @end_date > > ? > > Derrick > > > "Tango" <Tango@discussions.microsoft.com> wrote in message > news:3401F98B-707F-4FA2-BEB7-33699994222D@microsoft.com... > > Hi, > > I am querying a sybase database for my table & need to add a start date & > > end date to the query. > > my where statement looks like this > > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND > > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) > > > > can i please have some assistance around inserting the parameters > > start_date > > & end_date > > > > i have tried > > > > WHERE (ACDCallDetail.CallStartDt>=:start_date AND > > ACDCallDetail.CallStartDt<:end_date) and also WHERE > > (ACDCallDetail.CallStartDt>=@start_date AND > > ACDCallDetail.CallStartDt<@end_date) to no avaial. > > > > i also need to have the query that extracts the month only (for a month to > > date) out of say the start_date & the end_date (for month to date figures) > > > > Thankyou in adavance for any help > > > > todd > >
Todd,
In what environment are you running the query? Derrick [quoted text, click to view] "Tango" <Tango@discussions.microsoft.com> wrote in message news:6E1BE054-6750-4126-A455-643185384088@microsoft.com... > The parameters box comes up & i enter dates (tried both 01/08/2005 or > 01/08/2005 12:00 AM) & i get following error message > > must declare variable '@start_date' > > Thanks > > Todd > > "Derrick Van Hoeter" wrote: > >> What happens when you use >> >> WHERE ACDCallDetail.CallStartDt >= @start_date >> AND ACDCallDetail.CallStartDt < @end_date >> >> ? >> >> Derrick >> >> >> "Tango" <Tango@discussions.microsoft.com> wrote in message >> news:3401F98B-707F-4FA2-BEB7-33699994222D@microsoft.com... >> > Hi, >> > I am querying a sybase database for my table & need to add a start date >> > & >> > end date to the query. >> > my where statement looks like this >> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND >> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) >> > >> > can i please have some assistance around inserting the parameters >> > start_date >> > & end_date >> > >> > i have tried >> > >> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND >> > ACDCallDetail.CallStartDt<:end_date) and also WHERE >> > (ACDCallDetail.CallStartDt>=@start_date AND >> > ACDCallDetail.CallStartDt<@end_date) to no avaial. >> > >> > i also need to have the query that extracts the month only (for a month >> > to >> > date) out of say the start_date & the end_date (for month to date >> > figures) >> > >> > Thankyou in adavance for any help >> > >> > todd >> >> >>
Use
WHERE (ACDCallDetail.CallStartDt>=@start_date AND ACDCallDetail.CallStartDt<@end_date) You must add parameters to the report Called start_date and end_date ( use the same case as the where clause) Then open the data set and check the Parameters tab to ensure there is a mapping between the parameter and the variable.. To get a month value from a date in SQL select Datepart(mm,Getdate()) This returns the number of the month... -- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org [quoted text, click to view] "Tango" <Tango@discussions.microsoft.com> wrote in message news:3401F98B-707F-4FA2-BEB7-33699994222D@microsoft.com... > Hi, > I am querying a sybase database for my table & need to add a start date & > end date to the query. > my where statement looks like this > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) > > can i please have some assistance around inserting the parameters > start_date > & end_date > > i have tried > > WHERE (ACDCallDetail.CallStartDt>=:start_date AND > ACDCallDetail.CallStartDt<:end_date) and also WHERE > (ACDCallDetail.CallStartDt>=@start_date AND > ACDCallDetail.CallStartDt<@end_date) to no avaial. > > i also need to have the query that extracts the month only (for a month to > date) out of say the start_date & the end_date (for month to date figures) > > Thankyou in adavance for any help > > todd
Thanks Guys
I am using the where statement as suggested, the 2 parameters in the report. Still doesnt work. when i run the query in generic query designer, i get asked for parameters where i have tried entering date format dd/mm/yyyy 12:00 AM or just dd/mm/yyyy. I have also tried changing the parameter type from datetime to string & i get the following message Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare variable '@start_date'. Its no use using the sql query designer as the system just locks up when i have dates pre coded or gives error message when i attempt to run the query "Providor can not derive parameter information and setparameterinfo has not been called" Look forward to hearing from you. Todd Im not sure if this means anything but i am querying a sybase database via an ODBC (type) datasource. [quoted text, click to view] "Wayne Snyder" wrote:
> Use > WHERE > (ACDCallDetail.CallStartDt>=@start_date AND > ACDCallDetail.CallStartDt<@end_date) > > You must add parameters to the report Called start_date and end_date ( use > the same case as the where clause) > Then open the data set and check the Parameters tab to ensure there is a > mapping between the parameter and the variable.. > > To get a month value from a date in SQL > > select Datepart(mm,Getdate()) > > This returns the number of the month... > > -- > Wayne Snyder, MCDBA, SQL Server MVP > Mariner, Charlotte, NC > www.mariner-usa.com > (Please respond only to the newsgroups.) > > I support the Professional Association of SQL Server (PASS) and it's > community of SQL Server professionals. > www.sqlpass.org > > "Tango" <Tango@discussions.microsoft.com> wrote in message > news:3401F98B-707F-4FA2-BEB7-33699994222D@microsoft.com... > > Hi, > > I am querying a sybase database for my table & need to add a start date & > > end date to the query. > > my where statement looks like this > > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND > > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) > > > > can i please have some assistance around inserting the parameters > > start_date > > & end_date > > > > i have tried > > > > WHERE (ACDCallDetail.CallStartDt>=:start_date AND > > ACDCallDetail.CallStartDt<:end_date) and also WHERE > > (ACDCallDetail.CallStartDt>=@start_date AND > > ACDCallDetail.CallStartDt<@end_date) to no avaial. > > > > i also need to have the query that extracts the month only (for a month to > > date) out of say the start_date & the end_date (for month to date figures) > > > > Thankyou in adavance for any help > > > > todd > >
Todd,
I have received the same error message before when I have changed the query around the parameters. I don't have Reporting Service installed on this machine so I'm going from memory, but in the generic query design pane, there is a button to refresh data. Find that button and refresh the data, then try to run the query with the parameters again. You may still get an error but it should be a different error. Let me know how it goes. Derrick [quoted text, click to view] "Tango" <Tango@discussions.microsoft.com> wrote in message news:58575D27-7103-4748-86C4-D4B2D3CF27E9@microsoft.com... > Thanks Guys > > I am using the where statement as suggested, the 2 parameters in the > report. > Still doesnt work. > when i run the query in generic query designer, i get asked for parameters > where i have tried entering date format dd/mm/yyyy 12:00 AM or just > dd/mm/yyyy. I have also tried changing the parameter type from datetime to > string & i get the following message > Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare > variable '@start_date'. > Its no use using the sql query designer as the system just locks up when i > have dates pre coded or gives error message when i attempt to run the > query > "Providor can not derive parameter information and setparameterinfo has > not > been called" > > Look forward to hearing from you. > Todd > Im not sure if this means anything but i am querying a sybase database via > an ODBC (type) datasource. > > > "Wayne Snyder" wrote: > >> Use >> WHERE >> (ACDCallDetail.CallStartDt>=@start_date AND >> ACDCallDetail.CallStartDt<@end_date) >> >> You must add parameters to the report Called start_date and end_date ( >> use >> the same case as the where clause) >> Then open the data set and check the Parameters tab to ensure there is a >> mapping between the parameter and the variable.. >> >> To get a month value from a date in SQL >> >> select Datepart(mm,Getdate()) >> >> This returns the number of the month... >> >> -- >> Wayne Snyder, MCDBA, SQL Server MVP >> Mariner, Charlotte, NC >> www.mariner-usa.com >> (Please respond only to the newsgroups.) >> >> I support the Professional Association of SQL Server (PASS) and it's >> community of SQL Server professionals. >> www.sqlpass.org >> >> "Tango" <Tango@discussions.microsoft.com> wrote in message >> news:3401F98B-707F-4FA2-BEB7-33699994222D@microsoft.com... >> > Hi, >> > I am querying a sybase database for my table & need to add a start date >> > & >> > end date to the query. >> > my where statement looks like this >> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND >> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) >> > >> > can i please have some assistance around inserting the parameters >> > start_date >> > & end_date >> > >> > i have tried >> > >> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND >> > ACDCallDetail.CallStartDt<:end_date) and also WHERE >> > (ACDCallDetail.CallStartDt>=@start_date AND >> > ACDCallDetail.CallStartDt<@end_date) to no avaial. >> > >> > i also need to have the query that extracts the month only (for a month >> > to >> > date) out of say the start_date & the end_date (for month to date >> > figures) >> > >> > Thankyou in adavance for any help >> > >> > todd >> >> >>
Thanks for your interest Derrick & Tom,
in generic query designer the error message after entering dates is 'must declare variable '@start''. when i refresh the pane i get error message That data extension odbc does not support named paramters. use unnmaed paramaters instaed. Todd [quoted text, click to view] "Derrick Van Hoeter" wrote:
> Todd, > > I have received the same error message before when I have changed the query > around the parameters. I don't have Reporting Service installed on this > machine so I'm going from memory, but in the generic query design pane, > there is a button to refresh data. Find that button and refresh the data, > then try to run the query with the parameters again. You may still get an > error but it should be a different error. > > Let me know how it goes. > > Derrick > > > "Tango" <Tango@discussions.microsoft.com> wrote in message > news:58575D27-7103-4748-86C4-D4B2D3CF27E9@microsoft.com... > > Thanks Guys > > > > I am using the where statement as suggested, the 2 parameters in the > > report. > > Still doesnt work. > > when i run the query in generic query designer, i get asked for parameters > > where i have tried entering date format dd/mm/yyyy 12:00 AM or just > > dd/mm/yyyy. I have also tried changing the parameter type from datetime to > > string & i get the following message > > Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare > > variable '@start_date'. > > Its no use using the sql query designer as the system just locks up when i > > have dates pre coded or gives error message when i attempt to run the > > query > > "Providor can not derive parameter information and setparameterinfo has > > not > > been called" > > > > Look forward to hearing from you. > > Todd > > Im not sure if this means anything but i am querying a sybase database via > > an ODBC (type) datasource. > > > > > > "Wayne Snyder" wrote: > > > >> Use > >> WHERE > >> (ACDCallDetail.CallStartDt>=@start_date AND > >> ACDCallDetail.CallStartDt<@end_date) > >> > >> You must add parameters to the report Called start_date and end_date ( > >> use > >> the same case as the where clause) > >> Then open the data set and check the Parameters tab to ensure there is a > >> mapping between the parameter and the variable.. > >> > >> To get a month value from a date in SQL > >> > >> select Datepart(mm,Getdate()) > >> > >> This returns the number of the month... > >> > >> -- > >> Wayne Snyder, MCDBA, SQL Server MVP > >> Mariner, Charlotte, NC > >> www.mariner-usa.com > >> (Please respond only to the newsgroups.) > >> > >> I support the Professional Association of SQL Server (PASS) and it's > >> community of SQL Server professionals. > >> www.sqlpass.org > >> > >> "Tango" <Tango@discussions.microsoft.com> wrote in message > >> news:3401F98B-707F-4FA2-BEB7-33699994222D@microsoft.com... > >> > Hi, > >> > I am querying a sybase database for my table & need to add a start date > >> > & > >> > end date to the query. > >> > my where statement looks like this > >> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND > >> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'}) > >> > > >> > can i please have some assistance around inserting the parameters > >> > start_date > >> > & end_date > >> > > >> > i have tried > >> > > >> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND > >> > ACDCallDetail.CallStartDt<:end_date) and also WHERE > >> > (ACDCallDetail.CallStartDt>=@start_date AND > >> > ACDCallDetail.CallStartDt<@end_date) to no avaial. > >> > > >> > i also need to have the query that extracts the month only (for a month > >> > to > >> > date) out of say the start_date & the end_date (for month to date > >> > figures) > >> > > >> > Thankyou in adavance for any help > >> > > >> > todd > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |