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 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.
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
>

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