Passing datetime parameters to Reporting Services

Hi all...
How should I pass datetime parameters to a report in order for it to work?
This is the current scenario:
Dataset has, for example, this query:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN ? AND ?
where FECHA is of DATETIME type in an Oracle Database.
Both unnamed parameters are DateTime report parameters, which retrieve the
correct date values (using code embedded in the report)
When I run the report, I get the typical error message when date format is
incorrect:
"a non-numeric character was found where a numeric was expected"
It seems that report tries to pass the "-" of the date value to the unnamed
parameter.
Formerly I had this query that made the report to work:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
format). But using this way I can have (and I already had) problems with
incompatibilities in Regional Settings where the Reporting Server is
installed. For example, using this latter way, I forced the date to have the
format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
is installed, regional settings are different, so Report crashed because it
sent 08/31/2005 where database (in different server) expected 31/08/2005.
That's why I need to make it independent of regional settings of the target
server.
Any way to manage this?
Thanks a lot in advance
JaimeJaime
I believe this is an issue with the data being returned from the Query.
Not the rendering of the report.
If you run the report in the data tab and scroll to the bottom do you
get the same error?
You might want to take a look at the function Isdate(). Be advised this
will return valid dates so it will filter out any bad dates.
Sounds like someone put a character string in a date field. Does your
database allow this? Like an ASAP or somthing?
Leo
Jaime Stuardo wrote:
> Hi all...
> How should I pass datetime parameters to a report in order for it to work?
> This is the current scenario:
> Dataset has, for example, this query:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN ? AND ?
> where FECHA is of DATETIME type in an Oracle Database.
> Both unnamed parameters are DateTime report parameters, which retrieve the
> correct date values (using code embedded in the report)
> When I run the report, I get the typical error message when date format is
> incorrect:
> "a non-numeric character was found where a numeric was expected"
> It seems that report tries to pass the "-" of the date value to the unnamed
> parameter.
> Formerly I had this query that made the report to work:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> format). But using this way I can have (and I already had) problems with
> incompatibilities in Regional Settings where the Reporting Server is
> installed. For example, using this latter way, I forced the date to have the
> format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> is installed, regional settings are different, so Report crashed because it
> sent 08/31/2005 where database (in different server) expected 31/08/2005.
> That's why I need to make it independent of regional settings of the target
> server.
> Any way to manage this?
> Thanks a lot in advance
> Jaime
>|||Thanks Leo for answering...
When using the Data tab and run the query, I have to enter the date this
way:
30-apr-05 and query returns results.
The date field in database is correct, since as I told, when I use
TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
not DateTime as it should be. I cannot use the parameter as String as I
explained in my first post. In conclusion, the problem is only related to
passing parameters between Reporting Services and Oracle database.
Those DateTime parameters are calculated, for example, using this custom code:
Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
DateTime
Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
End Function
Finally, I'm going to tell you that I could display parameters just before
rendering the report (I'm using API to do it) and these are :
CUATRIMESTRE=1
AÃ?O=2005
NUMERO_POLIZA=5506666
FECHA_INICIO=5/1/2005 12:00:00 AM
FECHA_TERMINO=8/31/2005 12:00:00 AM
CUA_MESES=May - August
CUATRIMESTRE, AÃ?O and NUMERO_POLIZA are parameters the user enters.
FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
FECHA_INICIO and FECHA_TERMINO.
It's clear that Reporting Service is passing the datetime parameter formated
in a manner that isn't accepted by Oracle. I thought date time would be
passed as is, as a DATE datatype.
Any further help would be greately appreciated,
Thanks
Jaime
"cte25117@.centurytel.net" wrote:
> Jaime
> I believe this is an issue with the data being returned from the Query.
> Not the rendering of the report.
> If you run the report in the data tab and scroll to the bottom do you
> get the same error?
> You might want to take a look at the function Isdate(). Be advised this
> will return valid dates so it will filter out any bad dates.
> Sounds like someone put a character string in a date field. Does your
> database allow this? Like an ASAP or somthing?
> Leo
>
>
> Jaime Stuardo wrote:
> > Hi all...
> >
> > How should I pass datetime parameters to a report in order for it to work?
> > This is the current scenario:
> >
> > Dataset has, for example, this query:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN ? AND ?
> >
> > where FECHA is of DATETIME type in an Oracle Database.
> >
> > Both unnamed parameters are DateTime report parameters, which retrieve the
> > correct date values (using code embedded in the report)
> >
> > When I run the report, I get the typical error message when date format is
> > incorrect:
> > "a non-numeric character was found where a numeric was expected"
> >
> > It seems that report tries to pass the "-" of the date value to the unnamed
> > parameter.
> >
> > Formerly I had this query that made the report to work:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> >
> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> > format). But using this way I can have (and I already had) problems with
> > incompatibilities in Regional Settings where the Reporting Server is
> > installed. For example, using this latter way, I forced the date to have the
> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> > is installed, regional settings are different, so Report crashed because it
> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
> >
> > That's why I need to make it independent of regional settings of the target
> > server.
> >
> > Any way to manage this?
> > Thanks a lot in advance
> >
> > Jaime
> >
> >
>|||Have you tried CASTing the dates as Integers in the Oracel SQL query?
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:B960FCF6-7317-4197-8B76-55F79733BC71@.microsoft.com...
> Thanks Leo for answering...
> When using the Data tab and run the query, I have to enter the date this
> way:
> 30-apr-05 and query returns results.
> The date field in database is correct, since as I told, when I use
> TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
> not DateTime as it should be. I cannot use the parameter as String as I
> explained in my first post. In conclusion, the problem is only related to
> passing parameters between Reporting Services and Oracle database.
> Those DateTime parameters are calculated, for example, using this custom code:
> Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
> DateTime
> Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
> End Function
> Finally, I'm going to tell you that I could display parameters just before
> rendering the report (I'm using API to do it) and these are :
> CUATRIMESTRE=1
> AÑO=2005
> NUMERO_POLIZA=5506666
> FECHA_INICIO=5/1/2005 12:00:00 AM
> FECHA_TERMINO=8/31/2005 12:00:00 AM
> CUA_MESES=May - August
> CUATRIMESTRE, AÑO and NUMERO_POLIZA are parameters the user enters.
> FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
> CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
> FECHA_INICIO and FECHA_TERMINO.
> It's clear that Reporting Service is passing the datetime parameter formated
> in a manner that isn't accepted by Oracle. I thought date time would be
> passed as is, as a DATE datatype.
> Any further help would be greately appreciated,
> Thanks
> Jaime
> "cte25117@.centurytel.net" wrote:
>> Jaime
>> I believe this is an issue with the data being returned from the Query.
>> Not the rendering of the report.
>> If you run the report in the data tab and scroll to the bottom do you
>> get the same error?
>> You might want to take a look at the function Isdate(). Be advised this
>> will return valid dates so it will filter out any bad dates.
>> Sounds like someone put a character string in a date field. Does your
>> database allow this? Like an ASAP or somthing?
>> Leo
>>
>>
>> Jaime Stuardo wrote:
>> > Hi all...
>> >
>> > How should I pass datetime parameters to a report in order for it to work?
>> > This is the current scenario:
>> >
>> > Dataset has, for example, this query:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN ? AND ?
>> >
>> > where FECHA is of DATETIME type in an Oracle Database.
>> >
>> > Both unnamed parameters are DateTime report parameters, which retrieve the
>> > correct date values (using code embedded in the report)
>> >
>> > When I run the report, I get the typical error message when date format is
>> > incorrect:
>> > "a non-numeric character was found where a numeric was expected"
>> >
>> > It seems that report tries to pass the "-" of the date value to the unnamed
>> > parameter.
>> >
>> > Formerly I had this query that made the report to work:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
>> >
>> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
>> > format). But using this way I can have (and I already had) problems with
>> > incompatibilities in Regional Settings where the Reporting Server is
>> > installed. For example, using this latter way, I forced the date to have
>> > the
>> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
>> > is installed, regional settings are different, so Report crashed because it
>> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
>> >
>> > That's why I need to make it independent of regional settings of the target
>> > server.
>> >
>> > Any way to manage this?
>> > Thanks a lot in advance
>> >
>> > Jaime
>> >
>> >