Date Parameter

Have a report that requires a @.StartDate parameter. This will equal a
ActualDateTime fields in a table.
I have the following code listed in my where clause
and (tvo.ActualDateTime = @.StartDate)
but keeps getting throwing an error when i test. As we are in New Zealand
our date format is dd/MM/yyyy but when entering a start date in this format i
get an "arithmetic overflow error converting expression to date type
smalldatetime". I assume this is because the database is storing the field as
a datetime and its format is MM/dd/yyyy. I have set the parameter to
datatype datetime. I know this is probably easy to sort, just need a little
assistance.
Cheers.Are you getting this error AFTER you set the parameter to datetime?
It is understandable when the parameter is string, you'd have to format it
correctly before sending it to SQL..
--
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
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> Have a report that requires a @.StartDate parameter. This will equal a
> ActualDateTime fields in a table.
> I have the following code listed in my where clause
> and (tvo.ActualDateTime = @.StartDate)
> but keeps getting throwing an error when i test. As we are in New Zealand
> our date format is dd/MM/yyyy but when entering a start date in this
> format i
> get an "arithmetic overflow error converting expression to date type
> smalldatetime". I assume this is because the database is storing the field
> as
> a datetime and its format is MM/dd/yyyy. I have set the parameter to
> datatype datetime. I know this is probably easy to sort, just need a
> little
> assistance.
> Cheers.|||Cheers Wayne
I have run the report in the preview tab without the parameter statement in
the where clause and i get data returned.
The datatype of the datetime field that I need the @.StartDate parameter to
match is of smalldatetime type.
With the @.StartDate parameter set to datetime I get data returned, no
problem there. But only if i enter the date into the parameter box as
MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and have it display
the correct data.
hope this makes it a bit clearer.
i assume i have to convert the date time just not sure on the syntax required
"Wayne Snyder" wrote:
> Are you getting this error AFTER you set the parameter to datetime?
> It is understandable when the parameter is string, you'd have to format it
> correctly before sending it to SQL..
> --
> 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
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > Have a report that requires a @.StartDate parameter. This will equal a
> > ActualDateTime fields in a table.
> >
> > I have the following code listed in my where clause
> >
> > and (tvo.ActualDateTime = @.StartDate)
> >
> > but keeps getting throwing an error when i test. As we are in New Zealand
> > our date format is dd/MM/yyyy but when entering a start date in this
> > format i
> > get an "arithmetic overflow error converting expression to date type
> > smalldatetime". I assume this is because the database is storing the field
> > as
> > a datetime and its format is MM/dd/yyyy. I have set the parameter to
> > datatype datetime. I know this is probably easy to sort, just need a
> > little
> > assistance.
> >
> > Cheers.
>
>|||Check the code of your report. The second to last line in your XML will be:
<Language>en-US</Language>
Change it to:
<Language>en-NZ</Language>
Also ensure you have SP1 at least installed.
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> Cheers Wayne
> I have run the report in the preview tab without the parameter statement
in
> the where clause and i get data returned.
> The datatype of the datetime field that I need the @.StartDate parameter to
> match is of smalldatetime type.
> With the @.StartDate parameter set to datetime I get data returned, no
> problem there. But only if i enter the date into the parameter box as
> MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and have it
display
> the correct data.
> hope this makes it a bit clearer.
> i assume i have to convert the date time just not sure on the syntax
required
>
> "Wayne Snyder" wrote:
> > Are you getting this error AFTER you set the parameter to datetime?
> >
> > It is understandable when the parameter is string, you'd have to format
it
> > correctly before sending it to SQL..
> >
> > --
> > 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
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > Have a report that requires a @.StartDate parameter. This will equal a
> > > ActualDateTime fields in a table.
> > >
> > > I have the following code listed in my where clause
> > >
> > > and (tvo.ActualDateTime = @.StartDate)
> > >
> > > but keeps getting throwing an error when i test. As we are in New
Zealand
> > > our date format is dd/MM/yyyy but when entering a start date in this
> > > format i
> > > get an "arithmetic overflow error converting expression to date type
> > > smalldatetime". I assume this is because the database is storing the
field
> > > as
> > > a datetime and its format is MM/dd/yyyy. I have set the parameter to
> > > datatype datetime. I know this is probably easy to sort, just need a
> > > little
> > > assistance.
> > >
> > > Cheers.
> >
> >
> >|||Nat,
If your parameter is set to datetime, then there is a bug in the
preview tab that doesn't translate to dd/mm/yyyy it assumes US format.
I found the solution to be in the preview tab use yyyy-mm-dd, it seems
to be a universal format for SQL. DateTime is not 'stored' in any
national format, it's just a number which gets formatted based on
locale.
You'll probably find it works OK when deployed!
Chris
AshVsAOD wrote:
> Check the code of your report. The second to last line in your XML
> will be: <Language>en-US</Language>
> Change it to:
> <Language>en-NZ</Language>
>
> Also ensure you have SP1 at least installed.
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > Cheers Wayne
> >
> > I have run the report in the preview tab without the parameter
> > statement
> in
> > the where clause and i get data returned.
> >
> > The datatype of the datetime field that I need the @.StartDate
> > parameter to match is of smalldatetime type.
> >
> > With the @.StartDate parameter set to datetime I get data returned,
> > no problem there. But only if i enter the date into the parameter
> > box as MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and
> > have it
> display
> > the correct data.
> >
> > hope this makes it a bit clearer.
> >
> > i assume i have to convert the date time just not sure on the syntax
> required
> >
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Are you getting this error AFTER you set the parameter to
> > > datetime?
> > >
> > > It is understandable when the parameter is string, you'd have to
> > > format
> it
> > > correctly before sending it to SQL..
> > >
> > > --
> > > 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
> > >
> > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > message news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > Have a report that requires a @.StartDate parameter. This will
> > > > equal a ActualDateTime fields in a table.
> > > >
> > > > I have the following code listed in my where clause
> > > >
> > > > and (tvo.ActualDateTime = @.StartDate)
> > > >
> > > > but keeps getting throwing an error when i test. As we are in
> > > > New
> Zealand
> > > > our date format is dd/MM/yyyy but when entering a start date in
> > > > this format i
> > > > get an "arithmetic overflow error converting expression to date
> > > > type smalldatetime". I assume this is because the database is
> > > > storing the
> field
> > > > as
> > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > parameter to datatype datetime. I know this is probably easy to
> > > > sort, just need a little
> > > > assistance.
> > > >
> > > > Cheers.
> > >
> > >
> > >|||Thanks Chris
and you were right...works fine once deployed. just testing at preview
doesn't show correct date format...oh well at least it works...just wish i
hadn't spent so much time trying to fix something i couldn't.
have a good day...
"Chris McGuigan" wrote:
> Nat,
> If your parameter is set to datetime, then there is a bug in the
> preview tab that doesn't translate to dd/mm/yyyy it assumes US format.
> I found the solution to be in the preview tab use yyyy-mm-dd, it seems
> to be a universal format for SQL. DateTime is not 'stored' in any
> national format, it's just a number which gets formatted based on
> locale.
> You'll probably find it works OK when deployed!
> Chris
>
> AshVsAOD wrote:
> > Check the code of your report. The second to last line in your XML
> > will be: <Language>en-US</Language>
> >
> > Change it to:
> >
> > <Language>en-NZ</Language>
> >
> >
> >
> > Also ensure you have SP1 at least installed.
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > Cheers Wayne
> > >
> > > I have run the report in the preview tab without the parameter
> > > statement
> > in
> > > the where clause and i get data returned.
> > >
> > > The datatype of the datetime field that I need the @.StartDate
> > > parameter to match is of smalldatetime type.
> > >
> > > With the @.StartDate parameter set to datetime I get data returned,
> > > no problem there. But only if i enter the date into the parameter
> > > box as MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and
> > > have it
> > display
> > > the correct data.
> > >
> > > hope this makes it a bit clearer.
> > >
> > > i assume i have to convert the date time just not sure on the syntax
> > required
> > >
> > >
> > >
> > > "Wayne Snyder" wrote:
> > >
> > > > Are you getting this error AFTER you set the parameter to
> > > > datetime?
> > > >
> > > > It is understandable when the parameter is string, you'd have to
> > > > format
> > it
> > > > correctly before sending it to SQL..
> > > >
> > > > --
> > > > 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
> > > >
> > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > message news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > Have a report that requires a @.StartDate parameter. This will
> > > > > equal a ActualDateTime fields in a table.
> > > > >
> > > > > I have the following code listed in my where clause
> > > > >
> > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > >
> > > > > but keeps getting throwing an error when i test. As we are in
> > > > > New
> > Zealand
> > > > > our date format is dd/MM/yyyy but when entering a start date in
> > > > > this format i
> > > > > get an "arithmetic overflow error converting expression to date
> > > > > type smalldatetime". I assume this is because the database is
> > > > > storing the
> > field
> > > > > as
> > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > parameter to datatype datetime. I know this is probably easy to
> > > > > sort, just need a little
> > > > > assistance.
> > > > >
> > > > > Cheers.
> > > >
> > > >
> > > >
>|||I know what you mean! I found this out the hard way too!
If something doesn't seem right in preview, it's often worth deploying
and seeing if it's OK there. The rendering engine in Preview is not the
same as in Report Manager.
Chris
Nat Johnson wrote:
> Thanks Chris
> and you were right...works fine once deployed. just testing at
> preview doesn't show correct date format...oh well at least it
> works...just wish i hadn't spent so much time trying to fix
> something i couldn't.
> have a good day...
> "Chris McGuigan" wrote:
> > Nat,
> > If your parameter is set to datetime, then there is a bug in the
> > preview tab that doesn't translate to dd/mm/yyyy it assumes US
> > format. I found the solution to be in the preview tab use
> > yyyy-mm-dd, it seems to be a universal format for SQL. DateTime is
> > not 'stored' in any national format, it's just a number which gets
> > formatted based on locale.
> >
> > You'll probably find it works OK when deployed!
> >
> > Chris
> >
> >
> > AshVsAOD wrote:
> >
> > > Check the code of your report. The second to last line in your
> > > XML will be: <Language>en-US</Language>
> > >
> > > Change it to:
> > >
> > > <Language>en-NZ</Language>
> > >
> > >
> > >
> > > Also ensure you have SP1 at least installed.
> > >
> > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > message news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > > Cheers Wayne
> > > >
> > > > I have run the report in the preview tab without the parameter
> > > > statement
> > > in
> > > > the where clause and i get data returned.
> > > >
> > > > The datatype of the datetime field that I need the @.StartDate
> > > > parameter to match is of smalldatetime type.
> > > >
> > > > With the @.StartDate parameter set to datetime I get data
> > > > returned, no problem there. But only if i enter the date into
> > > > the parameter box as MM/dd/yyyy. I want to be able to enter it
> > > > as dd/MM/yyyy and have it
> > > display
> > > > the correct data.
> > > >
> > > > hope this makes it a bit clearer.
> > > >
> > > > i assume i have to convert the date time just not sure on the
> > > > syntax
> > > required
> > > >
> > > >
> > > >
> > > > "Wayne Snyder" wrote:
> > > >
> > > > > Are you getting this error AFTER you set the parameter to
> > > > > datetime?
> > > > >
> > > > > It is understandable when the parameter is string, you'd have
> > > > > to format
> > > it
> > > > > correctly before sending it to SQL..
> > > > >
> > > > > --
> > > > > 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
> > > > >
> > > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > > message
> > > > > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > > Have a report that requires a @.StartDate parameter. This
> > > > > > will equal a ActualDateTime fields in a table.
> > > > > >
> > > > > > I have the following code listed in my where clause
> > > > > >
> > > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > > >
> > > > > > but keeps getting throwing an error when i test. As we are
> > > > > > in New
> > > Zealand
> > > > > > our date format is dd/MM/yyyy but when entering a start
> > > > > > date in this format i
> > > > > > get an "arithmetic overflow error converting expression to
> > > > > > date type smalldatetime". I assume this is because the
> > > > > > database is storing the
> > > field
> > > > > > as
> > > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > > parameter to datatype datetime. I know this is probably
> > > > > > easy to sort, just need a little
> > > > > > assistance.
> > > > > >
> > > > > > Cheers.
> > > > >
> > > > >
> > > > >
> >
> >|||hi, i have this problem after i installed the SP 2 of Reporting Services,
anyone know if SP 2 modify something with the date format?
My reports use type string and not date time but with sp 1 run very well,
after the instalation of sp 2 comes the error "Arithmetic overflow error
converting expression to data type date..." when i put the parameter with the
format ddmmyyyy.
Anyone know where i can find information about this problem?
thank yoou very much!!!
Guillermo
"Chris McGuigan" wrote:
> I know what you mean! I found this out the hard way too!
> If something doesn't seem right in preview, it's often worth deploying
> and seeing if it's OK there. The rendering engine in Preview is not the
> same as in Report Manager.
> Chris
>
> Nat Johnson wrote:
> > Thanks Chris
> >
> > and you were right...works fine once deployed. just testing at
> > preview doesn't show correct date format...oh well at least it
> > works...just wish i hadn't spent so much time trying to fix
> > something i couldn't.
> >
> > have a good day...
> >
> > "Chris McGuigan" wrote:
> >
> > > Nat,
> > > If your parameter is set to datetime, then there is a bug in the
> > > preview tab that doesn't translate to dd/mm/yyyy it assumes US
> > > format. I found the solution to be in the preview tab use
> > > yyyy-mm-dd, it seems to be a universal format for SQL. DateTime is
> > > not 'stored' in any national format, it's just a number which gets
> > > formatted based on locale.
> > >
> > > You'll probably find it works OK when deployed!
> > >
> > > Chris
> > >
> > >
> > > AshVsAOD wrote:
> > >
> > > > Check the code of your report. The second to last line in your
> > > > XML will be: <Language>en-US</Language>
> > > >
> > > > Change it to:
> > > >
> > > > <Language>en-NZ</Language>
> > > >
> > > >
> > > >
> > > > Also ensure you have SP1 at least installed.
> > > >
> > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > message news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > > > Cheers Wayne
> > > > >
> > > > > I have run the report in the preview tab without the parameter
> > > > > statement
> > > > in
> > > > > the where clause and i get data returned.
> > > > >
> > > > > The datatype of the datetime field that I need the @.StartDate
> > > > > parameter to match is of smalldatetime type.
> > > > >
> > > > > With the @.StartDate parameter set to datetime I get data
> > > > > returned, no problem there. But only if i enter the date into
> > > > > the parameter box as MM/dd/yyyy. I want to be able to enter it
> > > > > as dd/MM/yyyy and have it
> > > > display
> > > > > the correct data.
> > > > >
> > > > > hope this makes it a bit clearer.
> > > > >
> > > > > i assume i have to convert the date time just not sure on the
> > > > > syntax
> > > > required
> > > > >
> > > > >
> > > > >
> > > > > "Wayne Snyder" wrote:
> > > > >
> > > > > > Are you getting this error AFTER you set the parameter to
> > > > > > datetime?
> > > > > >
> > > > > > It is understandable when the parameter is string, you'd have
> > > > > > to format
> > > > it
> > > > > > correctly before sending it to SQL..
> > > > > >
> > > > > > --
> > > > > > 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
> > > > > >
> > > > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > > > message
> > > > > > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > > > Have a report that requires a @.StartDate parameter. This
> > > > > > > will equal a ActualDateTime fields in a table.
> > > > > > >
> > > > > > > I have the following code listed in my where clause
> > > > > > >
> > > > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > > > >
> > > > > > > but keeps getting throwing an error when i test. As we are
> > > > > > > in New
> > > > Zealand
> > > > > > > our date format is dd/MM/yyyy but when entering a start
> > > > > > > date in this format i
> > > > > > > get an "arithmetic overflow error converting expression to
> > > > > > > date type smalldatetime". I assume this is because the
> > > > > > > database is storing the
> > > > field
> > > > > > > as
> > > > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > > > parameter to datatype datetime. I know this is probably
> > > > > > > easy to sort, just need a little
> > > > > > > assistance.
> > > > > > >
> > > > > > > Cheers.
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
>