Date parameters in ="select.......where creationdate="&...."

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
LudoWhere 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)|||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
"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
"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)
>|||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
>>
>|||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
"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
>>
>>
>