|
|
You're in the
sql server reporting services
group:Date parameters in ="select.......where creationdate="&...."
sql server reporting services:
If it is in report itself you can do as given belo: If you declare ReceiveDate as DateTime Type. you can write the sql statement directly . Select ........ Where creationdate = @ReceiveDate If you Declare it as String then you need to use date conversion function Select ........ Where creationdate = CDate(@ReceiveDate)
I think the issue here is that you are using an expression which is really a
mistake unless there is no other way. If you use the generic query designer (two pane) then just do this: select .... where creationdate = @ReceiveDate You don't have to do anything special. RS takes care of everything. If you use an expression then you have to do a lot more (for instance, embed single quotes). -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ludo Van Dun" <ludovandun@solectron.com> wrote in message news:u670fn90FHA.1256@TK2MSFTNGP09.phx.gbl... > Hi there, > I'm looking for the correct syntax for using datetime parameters in the > SQL statement. > > The parameter is called ReceiveDate and defaults to =Today() > > ="select ........ where creationdate = "&...... > > Thanks > Ludo >
Hi there,
I'm looking for the correct syntax for using datetime parameters in the SQL statement. The parameter is called ReceiveDate and defaults to =Today() ="select ........ where creationdate = "&...... Thanks Ludo
This is very odd. RS should be handling this. There is no reason for you to
have to use CDate or anything like that. Try changing your SQL to be like this: select part_number,serial_number from UNIT_STATUS_V where creation_time >= @StartDate and creation_time <= @EndDate You definitely should be able to have the parameter be a datetime. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ludo Van Dun" <ludovandun@solectron.com> wrote in message news:ONBhiqI1FHA.3780@TK2MSFTNGP12.phx.gbl... > Thanks, > it is in the report (RS 2005). It works when I put the parameter to string > and use query: > > select part_number,serial_number from UNIT_STATUS_V where creation_time > between @StartDate and @EndDate > > creation_time is a DateTime field. > > But I'd rather use the parameter as a DateTime as well since then I get a > calender pick control. > > I tried using CDate but with no result so far... > > Ludo > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > news:O0VYnH%230FHA.612@TK2MSFTNGP10.phx.gbl... >>I think the issue here is that you are using an expression which is really >>a mistake unless there is no other way. If you use the generic query >>designer (two pane) then just do this: >> >> select .... where creationdate = @ReceiveDate >> >> You don't have to do anything special. RS takes care of everything. If >> you use an expression then you have to do a lot more (for instance, embed >> single quotes). >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Ludo Van Dun" <ludovandun@solectron.com> wrote in message >> news:u670fn90FHA.1256@TK2MSFTNGP09.phx.gbl... >>> Hi there, >>> I'm looking for the correct syntax for using datetime parameters in the >>> SQL statement. >>> >>> The parameter is called ReceiveDate and defaults to =Today() >>> >>> ="select ........ where creationdate = "&...... >>> >>> Thanks >>> Ludo >>> >> >> > >
Thanks,
it is in the report (RS 2005). It works when I put the parameter to string and use query: select part_number,serial_number from UNIT_STATUS_V where creation_time between @StartDate and @EndDate creation_time is a DateTime field. But I'd rather use the parameter as a DateTime as well since then I get a calender pick control. I tried using CDate but with no result so far... Ludo [quoted text, click to view] "Rama Prasad" <RamaPrasad@discussions.microsoft.com> wrote in message news:827DCC76-9E60-40EF-9065-EBB1D9B4A335@microsoft.com... > Where you are writing the statemnt? In report or in stored procedure ? > > If it is in report itself you can do as given belo: > > If you declare ReceiveDate as DateTime Type. you can write the sql > statement > directly . > Select ........ Where creationdate = @ReceiveDate > > If you Declare it as String then you need to use date conversion function > Select ........ Where creationdate = CDate(@ReceiveDate) > >
Thanks,
it is in the report (RS 2005). It works when I put the parameter to string and use query: select part_number,serial_number from UNIT_STATUS_V where creation_time between @StartDate and @EndDate creation_time is a DateTime field. But I'd rather use the parameter as a DateTime as well since then I get a calender pick control. I tried using CDate but with no result so far... Ludo [quoted text, click to view] "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message news:O0VYnH%230FHA.612@TK2MSFTNGP10.phx.gbl... >I think the issue here is that you are using an expression which is really >a mistake unless there is no other way. If you use the generic query >designer (two pane) then just do this: > > select .... where creationdate = @ReceiveDate > > You don't have to do anything special. RS takes care of everything. If you > use an expression then you have to do a lot more (for instance, embed > single quotes). > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Ludo Van Dun" <ludovandun@solectron.com> wrote in message > news:u670fn90FHA.1256@TK2MSFTNGP09.phx.gbl... >> Hi there, >> I'm looking for the correct syntax for using datetime parameters in the >> SQL statement. >> >> The parameter is called ReceiveDate and defaults to =Today() >> >> ="select ........ where creationdate = "&...... >> >> Thanks >> Ludo >> > >
Hi again.....
thanks for your help so far, I found the problem and I'll try to explain. My date settings are set to british "dd/MM/yyyy". When I do the preview of the report the EndDate parameter defaults to =DateAdd("d",-1,Today()) and the StartDate to =Today(). The readable fields read 19/10/2005 and 20/10/2005 which look fine. When I click on the preview tab the report runs but when I click on view report the system returns an erro on the date parameter. Now...when I select a date in the calender picker the readable fields shows the date correctly like dd/MM/yyyy but the report returns the same error. But when I select fi 08/08/2005 till 09/08/2005 and I click view report the report runs and the 2 visible fields previously filled by the calender picker change to 08/08/2005 till 08/09/2005 so the reports runs from august 8th till september 8th ....I don't know the reason but when I deploy the report it seems to run correctly, so the problem only occurs in the preview..... Could this be a bug in the RS 2005 or do you know some settings I need to check ? Anyway many thanks for your help. Ludo Van Dun [quoted text, click to view] "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message news:eMgscQL1FHA.2540@TK2MSFTNGP09.phx.gbl... > This is very odd. RS should be handling this. There is no reason for you > to have to use CDate or anything like that. Try changing your SQL to be > like this: > > select part_number,serial_number from UNIT_STATUS_V where creation_time >= > @StartDate and creation_time <= @EndDate > > You definitely should be able to have the parameter be a datetime. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > > "Ludo Van Dun" <ludovandun@solectron.com> wrote in message > news:ONBhiqI1FHA.3780@TK2MSFTNGP12.phx.gbl... >> Thanks, >> it is in the report (RS 2005). It works when I put the parameter to >> string and use query: >> >> select part_number,serial_number from UNIT_STATUS_V where creation_time >> between @StartDate and @EndDate >> >> creation_time is a DateTime field. >> >> But I'd rather use the parameter as a DateTime as well since then I get a >> calender pick control. >> >> I tried using CDate but with no result so far... >> >> Ludo >> >> >> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message >> news:O0VYnH%230FHA.612@TK2MSFTNGP10.phx.gbl... >>>I think the issue here is that you are using an expression which is >>>really a mistake unless there is no other way. If you use the generic >>>query designer (two pane) then just do this: >>> >>> select .... where creationdate = @ReceiveDate >>> >>> You don't have to do anything special. RS takes care of everything. If >>> you use an expression then you have to do a lot more (for instance, >>> embed single quotes). >>> >>> >>> -- >>> Bruce Loehle-Conger >>> MVP SQL Server Reporting Services >>> >>> "Ludo Van Dun" <ludovandun@solectron.com> wrote in message >>> news:u670fn90FHA.1256@TK2MSFTNGP09.phx.gbl... >>>> Hi there, >>>> I'm looking for the correct syntax for using datetime parameters in the >>>> SQL statement. >>>> >>>> The parameter is called ReceiveDate and defaults to =Today() >>>> >>>> ="select ........ where creationdate = "&...... >>>> >>>> Thanks >>>> Ludo >>>> >>> >>> >> >> > >
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 |