Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?
The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!select convert(varchar(10), getdate(), 111)|||Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?
The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!
With the caveat that it's almost ALWAYS better to handle formatting on the client side, you might try:
Convert(varchar(10), [YourDate], 120)
Regards,
hmscott|||select convert(varchar(10), getdate(), 111)
Thanks! You've bailed me out of another toughee...
Showing posts with label yyyy. Show all posts
Showing posts with label yyyy. Show all posts
Monday, March 19, 2012
Formatting Date /Time in SQL 2000
I used to have an Access 2k query that formatted this date field:
Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
That gave me the date as this:
2006/03/30 00:00:01
Recently, we moved the data to a SQL 2000 table, so now I need to
create a view that gives me this date in that same format like I was
able to get it with that access query.
The actual date stored in the SQL table is 03/30/20006.
We use (convert(varchar,getdate(),101)) to get only the actual date
without the time.
Can anyone help me figure out how to get the same date format in SQL
Server 2000?
Thanks.SELECT CONVERT(CHAR(10), GETDATE(), 111);
http://www.aspfaq.com/2464
<ILCSP@.NETZERO.NET> wrote in message
news:1143819658.697747.320460@.j33g2000cwa.googlegroups.com...
>I used to have an Access 2k query that formatted this date field:
> Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
> That gave me the date as this:
> 2006/03/30 00:00:01
> Recently, we moved the data to a SQL 2000 table, so now I need to
> create a view that gives me this date in that same format like I was
> able to get it with that access query.
> The actual date stored in the SQL table is 03/30/20006.
> We use (convert(varchar,getdate(),101)) to get only the actual date
> without the time.
> Can anyone help me figure out how to get the same date format in SQL
> Server 2000?
> Thanks.
>|||First off, if you've changed to a SQL server 2000 back end I'd hope
you've moved to a sensible front end (like .NET) in which case this is
where you should be doing the formatting (.NET has a very easy and
powerful set of format strings).
otherwise, to my knowledge you can only use the set formats that
convert gives you, otherwise you'll have to build up your string using
CAST(DatePart(YEAR,mydate) as char(4)) + '/' + CAST(DATEPART(MONTH,
mydate) as char(2)) + '/' etc...
NOTE: it's important to cast them as strings, otherwise sql my
interpret this as an arithmetic expression and start dividing the year
by the month etc.
Cheers
Will|||SELECT replace(convert(varchar, getdate(), 120),'-','/') as todays_date|||Hi guys, I did use Aaron's date and then I added a string showing the
seconds and that worked out well..
Thanks for your suggestions.
By the way, yes, we're moving to .NET in the coming months.
Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
That gave me the date as this:
2006/03/30 00:00:01
Recently, we moved the data to a SQL 2000 table, so now I need to
create a view that gives me this date in that same format like I was
able to get it with that access query.
The actual date stored in the SQL table is 03/30/20006.
We use (convert(varchar,getdate(),101)) to get only the actual date
without the time.
Can anyone help me figure out how to get the same date format in SQL
Server 2000?
Thanks.SELECT CONVERT(CHAR(10), GETDATE(), 111);
http://www.aspfaq.com/2464
<ILCSP@.NETZERO.NET> wrote in message
news:1143819658.697747.320460@.j33g2000cwa.googlegroups.com...
>I used to have an Access 2k query that formatted this date field:
> Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
> That gave me the date as this:
> 2006/03/30 00:00:01
> Recently, we moved the data to a SQL 2000 table, so now I need to
> create a view that gives me this date in that same format like I was
> able to get it with that access query.
> The actual date stored in the SQL table is 03/30/20006.
> We use (convert(varchar,getdate(),101)) to get only the actual date
> without the time.
> Can anyone help me figure out how to get the same date format in SQL
> Server 2000?
> Thanks.
>|||First off, if you've changed to a SQL server 2000 back end I'd hope
you've moved to a sensible front end (like .NET) in which case this is
where you should be doing the formatting (.NET has a very easy and
powerful set of format strings).
otherwise, to my knowledge you can only use the set formats that
convert gives you, otherwise you'll have to build up your string using
CAST(DatePart(YEAR,mydate) as char(4)) + '/' + CAST(DATEPART(MONTH,
mydate) as char(2)) + '/' etc...
NOTE: it's important to cast them as strings, otherwise sql my
interpret this as an arithmetic expression and start dividing the year
by the month etc.
Cheers
Will|||SELECT replace(convert(varchar, getdate(), 120),'-','/') as todays_date|||Hi guys, I did use Aaron's date and then I added a string showing the
seconds and that worked out well..
Thanks for your suggestions.
By the way, yes, we're moving to .NET in the coming months.
Wednesday, March 7, 2012
Format parameter date string
I have a Analysis services dimension which is returning a date in the format
yyyy/mm/dd, I have added reporting filters (Parameters from-Date and to-Date
with datatype date to show calendar) for this date which is showing the
selected date in the format mm/dd/yyyy. How can I "format the parameter
values" or "write the expression" or "update MDX" so that the report shows
data within the filter dates?
Thanks and Regards
SridharFYI: I am using SQL Server 2005 and web-based reports using reporting
services.
--
Sridhar
>I have a Analysis services dimension which is returning a date in the
>format yyyy/mm/dd, I have added reporting filters (Parameters from-Date and
>to-Date with datatype date to show calendar) for this date which is showing
>the selected date in the format mm/dd/yyyy. How can I "format the parameter
>values" or "write the expression" or "update MDX" so that the report shows
>data within the filter dates?
>
> Thanks and Regards
> Sridhar
>
yyyy/mm/dd, I have added reporting filters (Parameters from-Date and to-Date
with datatype date to show calendar) for this date which is showing the
selected date in the format mm/dd/yyyy. How can I "format the parameter
values" or "write the expression" or "update MDX" so that the report shows
data within the filter dates?
Thanks and Regards
SridharFYI: I am using SQL Server 2005 and web-based reports using reporting
services.
--
Sridhar
>I have a Analysis services dimension which is returning a date in the
>format yyyy/mm/dd, I have added reporting filters (Parameters from-Date and
>to-Date with datatype date to show calendar) for this date which is showing
>the selected date in the format mm/dd/yyyy. How can I "format the parameter
>values" or "write the expression" or "update MDX" so that the report shows
>data within the filter dates?
>
> Thanks and Regards
> Sridhar
>
format parameter date
Hi,
I know how to format report items (dd/MM/yyyy) but how can i format a parameter in the format dd/MM/yyyy?
The current format is MM/dd/yyyy.
Thanks
Dim dt as String= "21/11/2007"
dim dtdate asa date
dim s() as string=dt.Split("/")
dtdate = s(1) & "/" & s(0) & "/" & s(2)
msgbox(dtdate)
Urs,
|||Thanks for the response. I am not sure where to put the code in the reporting services. I have parameters in the report but i am not sending any parameters to the report from aspx.
|||
Scroll down to Figure 17 on this pagehttp://www.eggheadcafe.com/articles/20040823.asp
Orhttp://msdn2.microsoft.com/en-us/library/ms157328.aspx
|||Hi,
What I need to format is the report parameter date (the value of the prompt) and not an actual textbox. In the prompt value the default dates appear in the format MM/dd/yyyy and i need the dates to appear as dd/MM/yyyy.
from_date 10/30/2007 to_date 12/30/2007 view report
Format parameter date
Hello
My problem is with parmater format date, when i input for example the date format yyyy/mm/dd and then press in boton view report this foramt change to dd/mm/yyyy, this succed in all machines with all configuration in the regional configuration.
When i change this format in th configuration of reporting services.
Thank you
In your query add a convert to your parameter field.
convert(datetime, @.parameter, 111)
|||Thank you for your request, but the problem isn't the database, i thnik the problem is the configuration of Reporting Services, because this software function with date format mm/dd/yyyy and i don't know when change this format. For example when i type in the paramater of the report 2006/08/06 (6 august 2006) and press the boton "show report" the screen refresh and then show 08/06/2006 (8 june 2006) and this i don't know why show. This is y problem.
Thank you
|||The interpretation of the value typed in for a date in the report parameters area is based on the client browser locale. The display of the date within the body of the report is based on the language property of the report (which can be bound to the client language if the report developer wants it to).
|||Are you setting the various lanuage settings for Reporting Services? These settings are very configurable. See the following link for more information.International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx
Sunday, February 26, 2012
format Now()...
How do I format Now() to look like dd/MM/yyyy (without time part) as a
default value for a report parameter?Now.ToShortDateString()
or
FormatDateTime(Now(),vbShortDate)
depending on where and when you use it.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||You can use Today() instead of Now(), but that simply displays the full date
& time with the time at midnight.
I was able to get closer with this as the default:
=Format(Today(),"dd/MM/yyyy")
But it's producing a type mismatch error if the parameter is a DateTime.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||If you don't want the user to see the time then you need to change the
parameter type to string. Then what Jeff says below will work. There is no
way if you have set the parameter type to datetime for it to not show the
time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You can use Today() instead of Now(), but that simply displays the full
date
> & time with the time at midnight.
> I was able to get closer with this as the default:
> =Format(Today(),"dd/MM/yyyy")
> But it's producing a type mismatch error if the parameter is a DateTime.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
> > default value for a report parameter?
>|||Use =Today() and set the field format to d.
Patrick
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:e%23xSbL64EHA.1596@.tk2msftngp13.phx.gbl...
> If you don't want the user to see the time then you need to change the
> parameter type to string. Then what Jeff says below will work. There is no
> way if you have set the parameter type to datetime for it to not show the
> time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> You can use Today() instead of Now(), but that simply displays the full
> date
>> & time with the time at midnight.
>> I was able to get closer with this as the default:
>> =Format(Today(),"dd/MM/yyyy")
>> But it's producing a type mismatch error if the parameter is a DateTime.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "clutch" <clutch@.discussions.microsoft.com> wrote in message
>> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
>> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
>> > default value for a report parameter?
>>
>
default value for a report parameter?Now.ToShortDateString()
or
FormatDateTime(Now(),vbShortDate)
depending on where and when you use it.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||You can use Today() instead of Now(), but that simply displays the full date
& time with the time at midnight.
I was able to get closer with this as the default:
=Format(Today(),"dd/MM/yyyy")
But it's producing a type mismatch error if the parameter is a DateTime.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||If you don't want the user to see the time then you need to change the
parameter type to string. Then what Jeff says below will work. There is no
way if you have set the parameter type to datetime for it to not show the
time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You can use Today() instead of Now(), but that simply displays the full
date
> & time with the time at midnight.
> I was able to get closer with this as the default:
> =Format(Today(),"dd/MM/yyyy")
> But it's producing a type mismatch error if the parameter is a DateTime.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
> > default value for a report parameter?
>|||Use =Today() and set the field format to d.
Patrick
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:e%23xSbL64EHA.1596@.tk2msftngp13.phx.gbl...
> If you don't want the user to see the time then you need to change the
> parameter type to string. Then what Jeff says below will work. There is no
> way if you have set the parameter type to datetime for it to not show the
> time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> You can use Today() instead of Now(), but that simply displays the full
> date
>> & time with the time at midnight.
>> I was able to get closer with this as the default:
>> =Format(Today(),"dd/MM/yyyy")
>> But it's producing a type mismatch error if the parameter is a DateTime.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "clutch" <clutch@.discussions.microsoft.com> wrote in message
>> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
>> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
>> > default value for a report parameter?
>>
>
Friday, February 24, 2012
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
> > > > > >
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
> > > > > >
Format Date in Reports
Dear friends
I have a problem with the format date, because my database has the format yyyy/mm/dd and in the computer user's the default configuration of date is dd/mm/yyyy, the problem exists with the reports that have the parameter of date type, the users input dd/mm/yyyy and the report request error in the format.
My question is, how do i make the any format input the users, i change in the report for this recieve the format yyyy/mm/dd, where is the parameter i have to change, the easy request is i have change the configuration of computer users, but i dont have this.
Thank you
Dear ,
U can try it like this
If u have a mtrix set report
=Day(First(Fields!OrderDate.Value, "MatrixSource"))& "/" & month(First(Fields!OrderDate.Value, "MatrixSource"))& "/" & year(First(Fields!OrderDate.Value, "MatrixSource"))
If not then remove the matrixsource text from the foruma
|||Thank you for your response, but how use this code in the parameters, because my problem is whit the parameter, the users input dd/mm/yyyy, but my database is configurate in yyyy/mm/dd, i don't know how i make this change from parameters to store procedure who is that recive the parameters.
Thank you
|||You can do the conversion in the stored procedure using the CAST or CONVERT functions (or in the command text, depending on the command text). Or you could convert it before hand in the expression for the query parameter value using the DateTime.Parse method along with ToString("yyyy\mm\dd"). For example,=DateTime.Parse(ReportParameters!DateParam.Value, New CultureInfo("fr-FR", False)).ToString("yyyy/mm/dd")
For more information:
http://msdn2.microsoft.com/en-us/library/kc8s65zs.aspx
For more information on CAST and CONVERT:
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
Ian
Sunday, February 19, 2012
format = yyyy/mm/dd h:m:s ?
How can i store the dates in a DateTime Columns in format = yyyy/mm/dd h:m:s --> 2005/01/21 18:40:21 ?
I have tried to write it in the formula field but it doesn't work .. it works in access 2000
for MS SQL 2000 database
thank youYou don't. You can retrieve datetimes in a particular format, but you can't (and don't have to) tell SQL Server how to store them.|||ok ! thank you|||...which is the same as in Access...
I have tried to write it in the formula field but it doesn't work .. it works in access 2000
for MS SQL 2000 database
thank youYou don't. You can retrieve datetimes in a particular format, but you can't (and don't have to) tell SQL Server how to store them.|||ok ! thank you|||...which is the same as in Access...
Subscribe to:
Posts (Atom)