Date range

What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @.Begin Date AND @.EndDate. I want to include all of the @.EndDate.well, BETWEEN is inclusive but your probably not receiving all of the enddates due to the time part of datetime datatype.

Another method is to add 1 day to the enddate (with no time indicator) and use:

WHERE date1 >= @.beginDate AND date1 < @.enddate

|||I used WHERE date1 BETWEEN @.Begin Date AND @.EndDate + '24:59:59' to include the data from the last day of the search.

So I assume there is no benefit from using between to < and >.|||Your query is not correct. It will exclude 1 second before midnight (SQL Server datetimes do include ms)

You should be using:

where Date1 >= @.Begin
 and Date1 < @.End + 1

OR

where convert(datetime, convert(varchar(10), Date1, 111), 111) = @.Begin

if it's only one day|||Using the CONVERT function would make the query non-sargable -- don't do it that way!! Definitely stick with Pierre's first option.

Terri|||Adding one to the date doesn't pose any issues? @.date + 1?|||As long as @.date does not contain any time data (ie it is the default midnight 00:00:00.000) that method should be fine.

Terri

date query problem in query analyzer

i tried to check my query in query analyzer even then its not giving the correct answer.
the query is

select status from t_auction where endDate > 12/5/2003

it gives me all four result and if i change greater than sign with less than then no result

heres the end dates
11/23/2003
11/27/2003
12/6/2003
12/24/2003Originally posted by waqas
i tried to check my query in query analyzer even then its not giving the correct answer.
the query is

select status from t_auction where endDate > 12/5/2003

it gives me all four result and if i change greater than sign with less than then no result

heres the end dates
11/23/2003
11/27/2003
12/6/2003
12/24/2003

Just enclose your date to a single quote
like
select status from t_auction where endDate > '12/5/2003'

and then try again...

-bernie|||thanx :)

date parameter in textbox

Hello,
I have two parameter one for Begindate and EndDate.
I was wondering how I can reference these two parameters so that it shows up
in a textbox.
I was hoping to have the textbox with the begin date and end date so that
when users print out the report they know that it is between those two
dates.
TIA,
JacksonHello:
I have done the following and I am not sure if thi8s method is for you or
not - but:
I included an additional line in my detail headers - that reference the
parameters passed for execution of the application.
You can define within the text boxes - the !Parameters passed - and define
as a field element - but just for the headings
Not sure if I was clear enough -
Warm regards,
"Jackson" wrote:
> Hello,
> I have two parameter one for Begindate and EndDate.
> I was wondering how I can reference these two parameters so that it shows up
> in a textbox.
> I was hoping to have the textbox with the begin date and end date so that
> when users print out the report they know that it is between those two
> dates.
> TIA,
> Jackson
>
>|||Thanks for replying back so fast.
Do you have the syntax for referencing the parameters?
If I had an parameter called begindate. would I type
Parameters!begindate.value or something like that?
Thanks again for the help,
Jackson
"code_slayer_bkk" <codeslayerbkk@.discussions.microsoft.com> wrote in message
news:7E9B23DD-D7BB-4077-A760-12BB61B549F0@.microsoft.com...
> Hello:
> I have done the following and I am not sure if thi8s method is for you or
> not - but:
> I included an additional line in my detail headers - that reference the
> parameters passed for execution of the application.
> You can define within the text boxes - the !Parameters passed - and define
> as a field element - but just for the headings
> Not sure if I was clear enough -
> Warm regards,
> "Jackson" wrote:
>> Hello,
>> I have two parameter one for Begindate and EndDate.
>> I was wondering how I can reference these two parameters so that it shows
>> up
>> in a textbox.
>> I was hoping to have the textbox with the begin date and end date so that
>> when users print out the report they know that it is between those two
>> dates.
>> TIA,
>> Jackson
>>|||Yes, that is how you would do it. But I suggest instead of typing you use
the expression editor. Right click on the textbox (or cell), select
Expression. You will be able to select the parameter you want to show. Note
that the name of your parameter is case sensitive. Also, sometimes you might
want the lable, not the value. If so then replace .value with .label (label
is not a choice with the expression builder).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:urcnkbYZGHA.504@.TK2MSFTNGP03.phx.gbl...
> Thanks for replying back so fast.
> Do you have the syntax for referencing the parameters?
> If I had an parameter called begindate. would I type
> Parameters!begindate.value or something like that?
> Thanks again for the help,
> Jackson
> "code_slayer_bkk" <codeslayerbkk@.discussions.microsoft.com> wrote in
> message news:7E9B23DD-D7BB-4077-A760-12BB61B549F0@.microsoft.com...
>> Hello:
>> I have done the following and I am not sure if thi8s method is for you or
>> not - but:
>> I included an additional line in my detail headers - that reference the
>> parameters passed for execution of the application.
>> You can define within the text boxes - the !Parameters passed - and
>> define
>> as a field element - but just for the headings
>> Not sure if I was clear enough -
>> Warm regards,
>> "Jackson" wrote:
>> Hello,
>> I have two parameter one for Begindate and EndDate.
>> I was wondering how I can reference these two parameters so that it
>> shows up
>> in a textbox.
>> I was hoping to have the textbox with the begin date and end date so
>> that
>> when users print out the report they know that it is between those two
>> dates.
>> TIA,
>> Jackson
>>
>|||Try this. = "Begin Date " & Parameters!Begindate.Value
"Jackson" wrote:
> Hello,
> I have two parameter one for Begindate and EndDate.
> I was wondering how I can reference these two parameters so that it shows up
> in a textbox.
> I was hoping to have the textbox with the begin date and end date so that
> when users print out the report they know that it is between those two
> dates.
> TIA,
> Jackson
>
>

Date Parameter in Reports Manager

I have one master report and 4 linked reports. I deployed a report and set defaults for BeginDate and EndDate as =Today(). I have 4 linked reports that I need to change the date parameters for each. For instance the first linked reports needs to be BeginDate =Today() and the EndDate as =Today()+30. So in my linked report, I tried to change the EndDate to =Today()+30 and an error, 'The value provided for the report parameter 'BeginDate' is not valid for its type.'

So I can't put =Today() or =Today()+30 in the parameters once I override the defaults. Can someone tell me what I'm missing here?

Thanks, Iris

Not sure if this will help but,

Try this:

=DateSerial(Year(Now), Month(Now), Day(Now)+30)

Instead of: Today()+30

|||I still got the same error. When I click 'override default' and put in the expression and click apply, I get this error.|||

When you say you have one master report and four linked reports. What does that mean?

The only way I can help you is to understand what your saying. Are you linking from within the master report by setting up four links using text boxes? Are you displaying data in a table and upon clicking on a field you are using the jump to report? What are your parameters initial settings for the StartDate and EndDate . ie(DateTime, String, etc...) I'm not sure just exactly what it is that your doing. If your attempting to pass a parameter value to a sub-report and change the default value, or setting up a subscription and over-riding the default value. Please explain further and I'll try to provide an answer.

|||

I've decided to use 'Available Values' in the Report Parameters area instead. Someone else tested and got the same error I did.

But basically, my idea came from an article located at www.databasejournal.com/features/mssql/article.php/3613751

Thanks,

Iris

Date Parameter in Reports Manager

I have one master report and 4 linked reports. I deployed a report and set defaults for BeginDate and EndDate as =Today(). I have 4 linked reports that I need to change the date parameters for each. For instance the first linked reports needs to be BeginDate =Today() and the EndDate as =Today()+30. So in my linked report, I tried to change the EndDate to =Today()+30 and an error, 'The value provided for the report parameter 'BeginDate' is not valid for its type.'

So I can't put =Today() or =Today()+30 in the parameters once I override the defaults. Can someone tell me what I'm missing here?

Thanks, Iris

Not sure if this will help but,

Try this:

=DateSerial(Year(Now), Month(Now), Day(Now)+30)

Instead of: Today()+30

|||I still got the same error. When I click 'override default' and put in the expression and click apply, I get this error.|||

When you say you have one master report and four linked reports. What does that mean?

The only way I can help you is to understand what your saying. Are you linking from within the master report by setting up four links using text boxes? Are you displaying data in a table and upon clicking on a field you are using the jump to report? What are your parameters initial settings for the StartDate and EndDate . ie(DateTime, String, etc...) I'm not sure just exactly what it is that your doing. If your attempting to pass a parameter value to a sub-report and change the default value, or setting up a subscription and over-riding the default value. Please explain further and I'll try to provide an answer.

|||

I've decided to use 'Available Values' in the Report Parameters area instead. Someone else tested and got the same error I did.

But basically, my idea came from an article located at www.databasejournal.com/features/mssql/article.php/3613751

Thanks,

Iris

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

sql

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format incorrectly interpreted

Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.

My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"

If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.

If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:

" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "

Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?

Thanks and Regards,

RenaHi guys,

I'm having the exact same problem. Any suggestions yet?

My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.

Tks ... Richard|||

I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.

I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.

Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.

Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview

Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.

Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.

I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)

See if it works for you.

Ian

|||

Hi,

Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!

Thanks again

John

|||

Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.

So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?

|||

If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.

Hope that helps,

-Lukasz

|||

Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:

1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).

2. Close Visual Studio 2005 and open it again.

3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)

4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.

Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?

Thanks,

Dan

|||

Hi Lukasz

I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.

In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).

In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.

Many thanks,

Mike.

|||

In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.

One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.

|||

Yes, Browser settings are set to UK English.

But browser settings shouldn't be part of the equation as the problem's in Visual Studio.

Anyway, thanks for your suggestion.

|||

Dear Lukasz,

After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.

I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).

I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.

Best regards and thanks in advance,

Jo?o Costa

Date parameter format

Hi All
I am using this formula
ToText({HECO_SP_GMWOUT_GROSS.DD},"MM/dd/yy") in ({?StartDate} to {?EndDate})
in Record selection formula for startdate and enddate parametrs, i need date format like this "MM/DD/YY".
And it throws me error like " "Bad Number format string"
Any solution please.
Thank youtry something like this:
CStr({HECO_SP_GMWOUT_GROSS.DD}, "yyyy MMM dd, dddd");

in ur case i guess it would be
CStr({HECO_SP_GMWOUT_GROSS.DD}, "MM/dd/yy");|||Hi Niro,
Thank you for ur reply but it still showing same error.
is there any other way.
Thankyou.|||which format are you sending parameter values?
You can also try using procedure that does the filtering and design the report based on that procedure|||I guess your start and end date parameters are strings (which happen to be dates in MM/dd/yy format) and your database column is a date?
Try converting your parameters into dates rather than your date into a string.

{HECO_SP_GMWOUT_GROSS.DD} in CDate({?StartDate}) to CDate({?EndDate})

You may need to use CDateTime instead of CDate, depending on the DD datatype.

You should also test the parameters using IsDate first.

Date parameter

My report has a StartDate and a EndDate parameters. I use the datetime
parameter type, and the input textbox shows both the date and time portions.
How to show only the date portion in the parameter textbox? By the way, is it
possible to have something like a datetime picker to get the date parameter?
The textbox control is not user-friendly.When you specify the parameter as date time, both with display although the
user may enter only the date or the time, with the remaing defaulting to the
min...
Some folks will use a string parameter instead...
You could populate the dates from a table in the database... But using a
date picker, you'd have to put an html page in front of the report and
include the date picker in that, then call the report from a web service.
--
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
"Matthew Chow" <Matthew Chow@.discussions.microsoft.com> wrote in message
news:025C4D51-7562-4532-ACE0-C5C39DF55D3E@.microsoft.com...
> My report has a StartDate and a EndDate parameters. I use the datetime
> parameter type, and the input textbox shows both the date and time
portions.
> How to show only the date portion in the parameter textbox? By the way, is
it
> possible to have something like a datetime picker to get the date
parameter?
> The textbox control is not user-friendly.|||On Mon, 24 Jan 2005 07:47:02 -0800, Matthew Chow <Matthew
Chow@.discussions.microsoft.com> wrote:
>My report has a StartDate and a EndDate parameters. I use the datetime
>parameter type, and the input textbox shows both the date and time portions.
>How to show only the date portion in the parameter textbox? By the way, is it
>possible to have something like a datetime picker to get the date parameter?
>The textbox control is not user-friendly.
DatePicker is rather "too much" to ask :))
As of date parameters, to avoid confusion among end-users with all
that extra time info, just use a string parameter with a prompt like
"Start Date (mm/dd/yyyy):" The proposed format should match the
locale of your server, so even foreign users (if you have any) won't
make mistakes. Then in your query use somethig like this:
WHERE MyTable.MyDate >= CONVERT(datetime, @.pStartDate)
If they enter something invalid, they will get a SQL error, like
"unable to convert to a datetime". This approach is less efficient,
because user's input is being validated on the database level, not on
the web page level, but at the same time provides a much cleaner way
for the users by avoiding confusion. We are now using this everywhere.|||Thanks for helping.sql

date input parameter

I have user who are entering startdate and enddate parameters. How can I make
sure that the date the enter is in the mm/dd/yy format? Usally you do this
with javascript. Or can I just do something where I can check the length of
the field.
--
kmatth007well the answer will be devide to 2:
1) on RS2000 where no calander/timepicker is availble you should define this
field as datetime and choose the wright format,then if the user will enter a
wrong date the RS won't load the report.no message avaible...
2)on RS2005 there's a timepicker so it's easy to know the user enter a
wright date.
But as recommanded a lot in this newsgroup the best way 2 controll the user
input is 2 use a .Net application that getts the input from the user and send
it to RS.
"kmatth007" wrote:
> I have user who are entering startdate and enddate parameters. How can I make
> sure that the date the enter is in the mm/dd/yy format? Usally you do this
> with javascript. Or can I just do something where I can check the length of
> the field.
> --
> kmatth007

Date headache

Guys
I have a table 1 row, a start and end date of a period

create table xperiod(startdate datetime , enddate datetime)
insert xperiod (startdate , enddate)
values ('2004-04-01 00:00:00.000' , 2012-03-31 00:00:00.000)

I'm trying to retrieve a batch of 'smaller' periods from this where the relevant period is a number (of months) passed as a parameter (only ever 1, 3 or 6)

for example, if the parameter is 1 I will obtain the following rows each being a 1 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
startperiod endperiod
'2004-04-01 00:00:00.000' '2004-04-30 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'

and so on to
'2012-03-01 00:00:00.000' '2012-03-31 00:00:00.000'

if the parameter is 3 I will obtain the following rows each being a 3 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value

startperiod endperiod
'2004-04-01 00:00:00.000' '2004-06-30 00:00:00.000'
'2004-07-01 00:00:00.000' '2004-09-30 00:00:00.000'
'2004-10-01 00:00:00.000' '2004-12-31 00:00:00.000'

and so on to
'2012-01-01 00:00:00.000' '2012-03-31 00:00:00.000'

Hope this makes sense !

I think I'll be ok on the logic for the while loop but my main problem is getting the endperiod value based on the startperiodvalue
Thx in advance--eg:for one month period
select dateadd(dd,-1,dateadd(mm,1,getdate())) as endperiod
--eg:for 3 month period
select dateadd(dd,-1,dateadd(mm,3,getdate())) as endperiod
--eg:for 6 month period
select dateadd(dd,-1,dateadd(mm,6,getdate())) as endperiod|||That's perfect - thanks

Date function to extract start date

Hello,
I was wondering if there is a date function to determine a starting
date based on a @.EndDate
parameter. In other word, if my user chose an end date of 3/31/2006, I
want to get a start date of 4/1/2005.
I appreciate any suggestion.
Edgar J.You can use another query to provide a default for the second parameter
using the 1st as input.
If its sqlserver you can use (I may have syntax slightly wrong as Im
not looking this up now).
SELECT [StartDate] = dateadd(day, dateadd(year, @.EndDate, -1), 1)
In other words return just 1 row , 1 column with a date.
Use sqlserver dateadd function to subtract 1 year and again to add 1
day.
Edgar wrote:
> Hello,
> I was wondering if there is a date function to determine a starting
> date based on a @.EndDate
> parameter. In other word, if my user chose an end date of 3/31/2006, I
> want to get a start date of 4/1/2005.
> I appreciate any suggestion.
> Edgar J.

date format problem

i have two fields, one is startDate and other is endDate, i need only date part in start date and date and time in end date, t shows correctly in db but when i write it in xml (using asp.net) the results are as

<startDate>2003-11-16T00:00:00.0000000+05:00</startDate>
<endDate>2003-11-24T00:00:00.0000000+05:00</endDate>That is the default display format for XML. You have a couple of options when dealing with dates in XML:

1. Write the data to XML as a character string (since you only need the date part, you could use SELECT Convert(Varchar(10), MyDate, 101).

2. When you display the XML (or parse it) you can use an XSL transform to format it (I think the XSL function is formatdate). There is a separate XSL function to handle time, IIRC.

Regards,

hmscott

Originally posted by waqas
i have two fields, one is startDate and other is endDate, i need only date part in start date and date and time in end date, t shows correctly in db but when i write it in xml (using asp.net) the results are as

<startDate>2003-11-16T00:00:00.0000000+05:00</startDate>
<endDate>2003-11-24T00:00:00.0000000+05:00</endDate>|||how to write it in sql query form
update t_auction set [status]= 'closed' where endDate < GETDATE()|||I'm sorry, I don't think I understand your question. Are you using the OPENXML method to get data from an XML document? Or is the XML date (2003-11-24T00:00:00.0000000+05:00) a value in a column in a SQL table that you are trying filter by?

Sorry, scratching my head on this one.

hmscott

Originally posted by waqas
how to write it in sql query form
update t_auction set [status]= 'closed' where endDate < GETDATE()|||i have to store the result in an xml file but before that i have to change the stauts from open to close whose date has been expired. in either cases i.e changing status and writing to xml file has this date probleem.|||can you post some more information? tables, scripts, etc.

thnx.

Originally posted by waqas
i have to store the result in an xml file but before that i have to change the stauts from open to close whose date has been expired. in either cases i.e changing status and writing to xml file has this date probleem.