Format date parameter input

Hi,
I have a report with 2 datetime parameters (a between). The report users
need to be able
to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
know how this can be done?
I have looked for this in all the topics about this at the forum, but there
is nobody with an answer. Is this possible at all in Reporting Services?
Gr.
Mike KlaarhamerThere must be several possible solutions. One is to define the parameter in
the report as a string type, then use it accordingly in your dataset query.
Charles Kangai, MCT, MCDBA
"Mike Klaarhamer" wrote:
> Hi,
> I have a report with 2 datetime parameters (a between). The report users
> need to be able
> to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> know how this can be done?
> I have looked for this in all the topics about this at the forum, but there
> is nobody with an answer. Is this possible at all in Reporting Services?
> Gr.
> Mike Klaarhamer
>|||Open report in visual studio, in the property grid select "report" and change
the "language" property to "English(United Kingdom)". That should do it.
"Mike Klaarhamer" wrote:
> Hi,
> I have a report with 2 datetime parameters (a between). The report users
> need to be able
> to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> know how this can be done?
> I have looked for this in all the topics about this at the forum, but there
> is nobody with an answer. Is this possible at all in Reporting Services?
> Gr.
> Mike Klaarhamer
>|||I had this problem once and I had to monkey around with both Paulb's
suggestion and setting the SQL Server up in as 'British' a way possible.
"Paulb" wrote:
> Open report in visual studio, in the property grid select "report" and change
> the "language" property to "English(United Kingdom)". That should do it.
> "Mike Klaarhamer" wrote:
> > Hi,
> >
> > I have a report with 2 datetime parameters (a between). The report users
> > need to be able
> > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > know how this can be done?
> > I have looked for this in all the topics about this at the forum, but there
> > is nobody with an answer. Is this possible at all in Reporting Services?
> >
> > Gr.
> >
> > Mike Klaarhamer
> >|||Hi,
Non of the suggested solutions worked :(
The report-language has nothing to do with the parameter-language, it stays
British.
Also when i convert the date to a string which has the good date-value, it
stays British in the parameter :(
Has anyone an other answer to this problem'
Thanx,
Mike Klaarhamer
"CraigyBoop" wrote:
> I had this problem once and I had to monkey around with both Paulb's
> suggestion and setting the SQL Server up in as 'British' a way possible.
> "Paulb" wrote:
> > Open report in visual studio, in the property grid select "report" and change
> > the "language" property to "English(United Kingdom)". That should do it.
> >
> > "Mike Klaarhamer" wrote:
> >
> > > Hi,
> > >
> > > I have a report with 2 datetime parameters (a between). The report users
> > > need to be able
> > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > know how this can be done?
> > > I have looked for this in all the topics about this at the forum, but there
> > > is nobody with an answer. Is this possible at all in Reporting Services?
> > >
> > > Gr.
> > >
> > > Mike Klaarhamer
> > >|||Mike, I'm relatively new to RS (like a lot of people) but what worked for me
was the following SQL statement:
SELECT CONVERT(varchar, GETDATE() - 30, 1) AS StartTimeWanted,
CONVERT(varchar, GETDATE(), 1) AS StopTimeWanted
First, I put this in dataset called dsDateSelect. Then I went to the Report
Parameters screen and added two report parameters. One named StartTimeWanted
and the other StopTimeWanted. These parameters are strings on the Report
Parameters screen and my dataset query is being used to get the default
values. (The first is 30 days prior to whatever is the current date and the
second is today's date)
Please note that I am using a Transact SQL statement against a SQL Server
2000 database and the supported function CONVERT uses the style 1 to format
the date in mm/dd/yy format. If I wanted four digits for the year, I would
change the style to 101. My language is set to US.
If you are using a database (Oracle, DB2, Informix, MySQL, etc.), I am not
sure what functions you can use to format the date in your dataset's SQL
statement but the above worked for me.
Thanks.
"Mike Klaarhamer" wrote:
> Hi,
> Non of the suggested solutions worked :(
> The report-language has nothing to do with the parameter-language, it stays
> British.
> Also when i convert the date to a string which has the good date-value, it
> stays British in the parameter :(
> Has anyone an other answer to this problem'
> Thanx,
> Mike Klaarhamer
> "CraigyBoop" wrote:
> > I had this problem once and I had to monkey around with both Paulb's
> > suggestion and setting the SQL Server up in as 'British' a way possible.
> >
> > "Paulb" wrote:
> >
> > > Open report in visual studio, in the property grid select "report" and change
> > > the "language" property to "English(United Kingdom)". That should do it.
> > >
> > > "Mike Klaarhamer" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a report with 2 datetime parameters (a between). The report users
> > > > need to be able
> > > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > > know how this can be done?
> > > > I have looked for this in all the topics about this at the forum, but there
> > > > is nobody with an answer. Is this possible at all in Reporting Services?
> > > >
> > > > Gr.
> > > >
> > > > Mike Klaarhamer
> > > >|||Mike, you also might check to see if Reporting Services Service Pack 1 has
been installed. I can't find the reference for it at this moment, but I did
read that SP1 does correct something with regard to the dd/mm/yy format.
Good luck and let us know how things turn out.
"Pat R." wrote:
> Mike, I'm relatively new to RS (like a lot of people) but what worked for me
> was the following SQL statement:
> SELECT CONVERT(varchar, GETDATE() - 30, 1) AS StartTimeWanted,
> CONVERT(varchar, GETDATE(), 1) AS StopTimeWanted
> First, I put this in dataset called dsDateSelect. Then I went to the Report
> Parameters screen and added two report parameters. One named StartTimeWanted
> and the other StopTimeWanted. These parameters are strings on the Report
> Parameters screen and my dataset query is being used to get the default
> values. (The first is 30 days prior to whatever is the current date and the
> second is today's date)
> Please note that I am using a Transact SQL statement against a SQL Server
> 2000 database and the supported function CONVERT uses the style 1 to format
> the date in mm/dd/yy format. If I wanted four digits for the year, I would
> change the style to 101. My language is set to US.
> If you are using a database (Oracle, DB2, Informix, MySQL, etc.), I am not
> sure what functions you can use to format the date in your dataset's SQL
> statement but the above worked for me.
> Thanks.
> "Mike Klaarhamer" wrote:
> > Hi,
> >
> > Non of the suggested solutions worked :(
> > The report-language has nothing to do with the parameter-language, it stays
> > British.
> > Also when i convert the date to a string which has the good date-value, it
> > stays British in the parameter :(
> > Has anyone an other answer to this problem'
> >
> > Thanx,
> >
> > Mike Klaarhamer
> >
> > "CraigyBoop" wrote:
> >
> > > I had this problem once and I had to monkey around with both Paulb's
> > > suggestion and setting the SQL Server up in as 'British' a way possible.
> > >
> > > "Paulb" wrote:
> > >
> > > > Open report in visual studio, in the property grid select "report" and change
> > > > the "language" property to "English(United Kingdom)". That should do it.
> > > >
> > > > "Mike Klaarhamer" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a report with 2 datetime parameters (a between). The report users
> > > > > need to be able
> > > > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > > > know how this can be done?
> > > > > I have looked for this in all the topics about this at the forum, but there
> > > > > is nobody with an answer. Is this possible at all in Reporting Services?
> > > > >
> > > > > Gr.
> > > > >
> > > > > Mike Klaarhamer
> > > > >|||Hi I also use the CONVERT function the following works for me: -
I Create an input paramter as string type linked to a dataset which provides
the source with the string formated as follows dd MMM YYYY
Then CONVERT(DATETIME,@.fDate,102) or CONVERT(DATETIME,@.fDate,103)
These tend to format the parameter as follows: -
YYYY/MM/DD or YYY/DD/MM depending on input
Regards
"Pat R." wrote:
> Mike, you also might check to see if Reporting Services Service Pack 1 has
> been installed. I can't find the reference for it at this moment, but I did
> read that SP1 does correct something with regard to the dd/mm/yy format.
> Good luck and let us know how things turn out.
> "Pat R." wrote:
> > Mike, I'm relatively new to RS (like a lot of people) but what worked for me
> > was the following SQL statement:
> >
> > SELECT CONVERT(varchar, GETDATE() - 30, 1) AS StartTimeWanted,
> > CONVERT(varchar, GETDATE(), 1) AS StopTimeWanted
> >
> > First, I put this in dataset called dsDateSelect. Then I went to the Report
> > Parameters screen and added two report parameters. One named StartTimeWanted
> > and the other StopTimeWanted. These parameters are strings on the Report
> > Parameters screen and my dataset query is being used to get the default
> > values. (The first is 30 days prior to whatever is the current date and the
> > second is today's date)
> >
> > Please note that I am using a Transact SQL statement against a SQL Server
> > 2000 database and the supported function CONVERT uses the style 1 to format
> > the date in mm/dd/yy format. If I wanted four digits for the year, I would
> > change the style to 101. My language is set to US.
> >
> > If you are using a database (Oracle, DB2, Informix, MySQL, etc.), I am not
> > sure what functions you can use to format the date in your dataset's SQL
> > statement but the above worked for me.
> >
> > Thanks.
> >
> > "Mike Klaarhamer" wrote:
> >
> > > Hi,
> > >
> > > Non of the suggested solutions worked :(
> > > The report-language has nothing to do with the parameter-language, it stays
> > > British.
> > > Also when i convert the date to a string which has the good date-value, it
> > > stays British in the parameter :(
> > > Has anyone an other answer to this problem'
> > >
> > > Thanx,
> > >
> > > Mike Klaarhamer
> > >
> > > "CraigyBoop" wrote:
> > >
> > > > I had this problem once and I had to monkey around with both Paulb's
> > > > suggestion and setting the SQL Server up in as 'British' a way possible.
> > > >
> > > > "Paulb" wrote:
> > > >
> > > > > Open report in visual studio, in the property grid select "report" and change
> > > > > the "language" property to "English(United Kingdom)". That should do it.
> > > > >
> > > > > "Mike Klaarhamer" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a report with 2 datetime parameters (a between). The report users
> > > > > > need to be able
> > > > > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > > > > know how this can be done?
> > > > > > I have looked for this in all the topics about this at the forum, but there
> > > > > > is nobody with an answer. Is this possible at all in Reporting Services?
> > > > > >
> > > > > > Gr.
> > > > > >
> > > > > > Mike Klaarhamer
> > > > > >