Date Picker in US Format after SP2

Updated SQL 2005 to SP2 and Reporting Services Date Picker now has dates defaulting to US format (MM/DD/YYYY) instead of our previous English-Australian (DD/MM/YYYY). Both the clients and server are set to English Australian regional settings in the O/S and the reports are composed for English Australian language. Reports have correct format in Visual Studio development and re-deploying them had no effect. Everything was fine under SP1 - not sure what is going on. Any ideas?

I have had so much trouble with date fields that I now keep them as text fields in Reporting Services and pass then through to SQL Server as VarChars(). Using three letters for the month (Jan, Feb, Mar etc) is the best way to may sure that SQL Server gets it right. I always set a default in the format "12 Apr 07 13:00" so the use sees what format is required. This means not using the date control but in my applications we time as well as date and the date control doesn't do this anyway.

The problem seems to be that there are many places to set the "country" or "language" etc.

Places that I can think of are:-

1. Control Panel/Regonal and Language Settings.

2. SQL Server using Set Language (or Set DateFormat). I believe that this affects the current session and so must be used in every report query.

3. Intenet Explorer, which is relevant when accessing Report Server.

I am not sure which SP I have but the patch level is 9.1.2153.

|||After doing some more analysis; I have found that if a datetime parameter prompt is placed after two parameters that cascade, then the correct date format (in our case English-Australian DD/MM/YYYY) is used in the Date Picker (probably due to the language settings in the client and report). Otherwise you get the US Date format (MM/DD/YYYY). This means that if you have datetime parameters on their own or even with drop downs, multi-selects etc.. then you could be in for trouble. I could be mistaken but this seems like a bug in SQL 2005 SP2.|||The problem has now been narrowed down to the Reporting Services Sharepoint web parts: rswebparts.cab (v2.0). Reports run with the correct date picker format from the report server but are incorrect if they are run from the SharePoint report viewer web part. I believe these web parts were not updated in SP2 (see http://msdn2.microsoft.com/en-us/library/ms159772.aspx)|||We get this same problem outside of sharepoint after sp2. If you go directly to the reportserver (http://yourserver/reportserver) and navigate to the report you may find the same. This problem has surfaced after the sp2 upgrade - strange thing is that is does not occur if we run the report via report manager - I expect why the sharepoint is showing the problem is because it is making a direct call to the reportserver.

Date Parameter ERROR

My RS has two date parameters setup as a date data type. If the user was to
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.

Date formatting

Is there a function to return the date in the format of "4 July 2007" instead. I am returning rows which contain dates in the format "20/07/2007 10:48:16". Is there any function that convert, or will I have to write the code myself? I dont mind writing the code, but its a friday evening, and I am lazy and tired :-)
You can get close enough with CONVERT

eg, convert(varchar, getdate(),106)

To get the full month name though i believe you'd have to write something bespoke.

HTH.
|||

Yes. You have to write your own..

Select Cast(day(getdate()) as Varchar)

+ ' '

+ datename(month, getdate())

+ ' '

+ Cast(year(getdate()) as varchar)

|||As Mani indicated, create your own function, pass in the date, and get it back just like you want it.

Date format reloaded

Hi all.
How can i make sql 2000 server return dd/mm/yyyy instead of mm/dd/yyyy?
I know i can make formatting functions for that, but in this case i need to
set
the sql server locale correctly.
Thanks.
Sharon.SQL Server doesn't format datetime results, the client application does. So
you need to look for a
setting in the client app you are using. Some client application respect the
Regional Settings on
the machine where you run the client app. More info at:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sharon" <nothing@.null.void> wrote in message news:eEp0uprDGHA.1288@.TK2MSFTNGP09.phx.gbl...

> Hi all.
> How can i make sql 2000 server return dd/mm/yyyy instead of mm/dd/yyyy?
> I know i can make formatting functions for that, but in this case i need t
o set
> the sql server locale correctly.
> Thanks.
> --
> Sharon.
>|||Thanks Tibor.
The problem was in the data source settings.
Sharon.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CuxEurDGHA.2300@.TK2MSFTNGP15.phx.gbl...
> SQL Server doesn't format datetime results, the client application does.
> So you need to look for a setting in the client app you are using. Some
> client application respect the Regional Settings on the machine where you
> run the client app. More info at:
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sharon" <nothing@.null.void> wrote in message
> news:eEp0uprDGHA.1288@.TK2MSFTNGP09.phx.gbl...
>

Date Format accepting MDY instead of DMY

I have a unbound text box that gets its date from the system (date.now).

The problem is that when I write it to SQL (via a SQL Insert Command) It throws an error.

It transpires that the format is wrong. It accepts the date such as 07/17/2007 just fine but not 17/07/2007 which is automatically generated.

My IIS has a locale setting with is correct for the UK.

How can I change SQL 2005 so that it accepts DMY for a date/time field

Thanks.

Hmmm,

I actually fixed this already. As my date box was to be updated by the system clock and not the user I thought the easiest thing would be to format the date in US format before submission.

I used this VB code:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

'Create a var. named rightNow and set it to the current date/time

Dim rightNowAs DateTime =Date.Now

Dim sAsString'create a string

s = rightNow.ToString("MM/dd/yyyy hh:mm:ss")

LogDate.Text = s

EndSub

That has done the trick.

But would like to know how to change SQL anyway.

|||

http://msdn2.microsoft.com/en-us/library/aa259188(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms174398.aspx -- probably better

If you search hard enough, there are a couple of ways of specifying it in your connection string as well. I believe (but could be wrong) that it's like:

Persist Security Info=False;Trusted_Connection=True;database=AdventureWorks;server=(local);Language=British

Date Format

How can I format my date within Reporting Services to display like this:
Monday, January 9, 2006
instead of like this: 1/9/2006 12:00:00 AMNevermind, I found the answer. The answer is put a "D" to show the long date
in the format property.
"David" wrote:
> How can I format my date within Reporting Services to display like this:
> Monday, January 9, 2006
> instead of like this: 1/9/2006 12:00:00 AM|||Hi
I need to know how to display current date in the report.
Do i need to do something with rdl.
I wrote one JScript function there for the display of the date but its not
working.
As I am using xslt also at the render of the report for the foprmatting
purposes, so I wrote the same Jscript there also but still it is not working.
Can u please guide me.
Thanks in advance.
--
Vishnu
"David" wrote:
> Nevermind, I found the answer. The answer is put a "D" to show the long date
> in the format property.
> "David" wrote:
> > How can I format my date within Reporting Services to display like this:
> >
> > Monday, January 9, 2006
> >
> > instead of like this: 1/9/2006 12:00:00 AM

Date again

I changed my datefunction to look like this instead, but I stil want to add a parameter so that the user can decide if it shoulb be 2003 or 03. I've been trying back and forward but can't get anywhere. Please se I YOU can help!

create function SvensktDatum3(@.indate datetime)
returns nchar(20)
as
begin
return
CONVERT(CHAR(10), @.indate, 126)
end

select dbo.SvensktDatum3(getdate())Can you explain a little bit more what you want to do?
From what i realise it seams that you can put a flag on the function tha could be 0 and 1. 0 to use a four digit year and 1 to use a two digit year.

Paulo

Originally posted by buggirl
I changed my datefunction to look like this instead, but I stil want to add a parameter so that the user can decide if it shoulb be 2003 or 03. I've been trying back and forward but can't get anywhere. Please se I YOU can help!

create function SvensktDatum3(@.indate datetime)
returns nchar(20)
as
begin
return
CONVERT(CHAR(10), @.indate, 126)
end

select dbo.SvensktDatum3(getdate())|||I want to be able to show the year with 2 or 4 digits depending on a value I give when I call the function.|||Use a bit flag.
You save the date with four digit year in the table database, then you pass the parametter 1 or 0 and do the right cast for the date.

Paulo

Originally posted by buggirl
I want to be able to show the year with 2 or 4 digits depending on a value I give when I call the function.|||Try this:

alter function SvensktDatum3(@.indate datetime,@.cent tinyint)
-- @.cent<>1 - 2 digits
-- @.cent=1 - 4 digits
returns nchar(20)
as
begin
return CONVERT(CHAR(10), @.indate, case @.cent when 1 then 102 else 2 end)
end|||this worked just fine, but I want the format to be 2003-05-15 not 2003.05.15.
How do I do that?|||Originally posted by buggirl
this worked just fine, but I want the format to be 2003-05-15 not 2003.05.15.
How do I do that?

If you want to see this format in application - change format of date on application level or change settings on computer.
if you want to see this in QA or EM - it is possible to do but why you need it.|||This is my solution:

create function SwedishDate(@.indate datetime, @.separator char(1), @.year_digits int)
returns nchar(20)
as
begin
declare @.manad nvarchar(20), @.dag nvarchar(20), @.year nvarchar(10)
set @.manad = right('00' + cast((datepart(mm, @.indate)) as varchar(2)), 2) + @.separator
set @.dag = right('00' + cast((datepart(dd, @.indate)) as varchar(2)), 2)
if @.year_digits = 2
set @.year = reverse(cast(reverse(ltrim(convert(nvarchar(20), datepart(yyyy, @.indate)))) as char(2)))
else
set @.year = convert(nvarchar(20), datepart(yyyy, @.indate))
return @.year + @.separator + @.manad + @.dag
end

call it like this:

select dbo.SwedishDate(getdate(), '-', 2)

Thanks for all the help on the way!

Originally posted by snail
If you want to see this format in application - change format of date on application level or change settings on computer.
if you want to see this in QA or EM - it is possible to do but why you need it.