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 - SQL Server 2000

If date format is set by default language and the default language is set to
English and English has a mdy format, why would dates come out as ymd when
queried? Is it possible to set the date format on server level so that
whenever a user runs a query it comes out in mm/dd/yyyy format? I know I can
convert it to string then change the format but I don't want that option.
Please help..."Bob" <Bob@.discussions.microsoft.com> wrote in message
news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> If date format is set by default language and the default language is set
> to
> English and English has a mdy format, why would dates come out as ymd when
> queried? Is it possible to set the date format on server level so that
> whenever a user runs a query it comes out in mm/dd/yyyy format? I know I
> can
> convert it to string then change the format but I don't want that option.
> Please help...
SQL Server has no control over how dates are displayed in your client
application. You need to configure that setting client-side, either in the
Windows Control Panel or in the client application or your development
environment.
SQL Server does have the LANGUAGE and DATEFORMAT settings but they purely
control how strings get converted to dates. Those settings do not control
how dates get displayed by client applications.
You could convert your dates to strings and return those string values to
the client as a way of controlling the format. I think that's a bad idea
however. For one thing it means you can't maniplulate the values as proper
dates any more. For another, IMO it is inconsiderate to force the user to
live with the format that you prefer.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's what I thought too. Just wanted to confirm. Thanks for your help.
"David Portas" wrote:
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> > If date format is set by default language and the default language is set
> > to
> > English and English has a mdy format, why would dates come out as ymd when
> > queried? Is it possible to set the date format on server level so that
> > whenever a user runs a query it comes out in mm/dd/yyyy format? I know I
> > can
> > convert it to string then change the format but I don't want that option.
> > Please help...
> SQL Server has no control over how dates are displayed in your client
> application. You need to configure that setting client-side, either in the
> Windows Control Panel or in the client application or your development
> environment.
> SQL Server does have the LANGUAGE and DATEFORMAT settings but they purely
> control how strings get converted to dates. Those settings do not control
> how dates get displayed by client applications.
> You could convert your dates to strings and return those string values to
> the client as a way of controlling the format. I think that's a bad idea
> however. For one thing it means you can't maniplulate the values as proper
> dates any more. For another, IMO it is inconsiderate to force the user to
> live with the format that you prefer.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||You can set the date format on a session level..
SET DATEFORMAT ydm
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> If date format is set by default language and the default language is set
> to
> English and English has a mdy format, why would dates come out as ymd when
> queried? Is it possible to set the date format on server level so that
> whenever a user runs a query it comes out in mm/dd/yyyy format? I know I
> can
> convert it to string then change the format but I don't want that option.
> Please help...

Date Format - SQL Server 2000

If date format is set by default language and the default language is set to
English and English has a mdy format, why would dates come out as ymd when
queried? Is it possible to set the date format on server level so that
whenever a user runs a query it comes out in mm/dd/yyyy format? I know I can
convert it to string then change the format but I don't want that option.
Please help..."Bob" <Bob@.discussions.microsoft.com> wrote in message
news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> If date format is set by default language and the default language is set
> to
> English and English has a mdy format, why would dates come out as ymd when
> queried? Is it possible to set the date format on server level so that
> whenever a user runs a query it comes out in mm/dd/yyyy format? I know I
> can
> convert it to string then change the format but I don't want that option.
> Please help...
SQL Server has no control over how dates are displayed in your client
application. You need to configure that setting client-side, either in the
Windows Control Panel or in the client application or your development
environment.
SQL Server does have the LANGUAGE and DATEFORMAT settings but they purely
control how strings get converted to dates. Those settings do not control
how dates get displayed by client applications.
You could convert your dates to strings and return those string values to
the client as a way of controlling the format. I think that's a bad idea
however. For one thing it means you can't maniplulate the values as proper
dates any more. For another, IMO it is inconsiderate to force the user to
live with the format that you prefer.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's what I thought too. Just wanted to confirm. Thanks for your help.
"David Portas" wrote:

> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> SQL Server has no control over how dates are displayed in your client
> application. You need to configure that setting client-side, either in the
> Windows Control Panel or in the client application or your development
> environment.
> SQL Server does have the LANGUAGE and DATEFORMAT settings but they purely
> control how strings get converted to dates. Those settings do not control
> how dates get displayed by client applications.
> You could convert your dates to strings and return those string values to
> the client as a way of controlling the format. I think that's a bad idea
> however. For one thing it means you can't maniplulate the values as proper
> dates any more. For another, IMO it is inconsiderate to force the user to
> live with the format that you prefer.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||You can set the date format on a session level..
SET DATEFORMAT ydm
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:92CD54E6-1182-410B-A995-973737A7238D@.microsoft.com...
> If date format is set by default language and the default language is set
> to
> English and English has a mdy format, why would dates come out as ymd when
> queried? Is it possible to set the date format on server level so that
> whenever a user runs a query it comes out in mm/dd/yyyy format? I know I
> can
> convert it to string then change the format but I don't want that option.
> Please help...

Date and Language settings - SQL Server 2005

Despite the documentatin stating that SQL Server will pick up and use the locale settings, why will the database only accept entry in american (mdy) format?

MDY is the default datetime format in SQL Server.

You can change the format using "SET DATEFORMAT",

see help here: http://msdn2.microsoft.com/en-us/library/ms189491.aspx

Xinwei

|||Thanks,
I tried that and it didn't appear to do anything. I've got the ldefault language settings as British and all the examples were coming out in dmy displayed format despite changing the format.
The documentation (SQL Books) states that it should use the server locale.
I solved the problem using code to transform the date from a 'DateTime picker' into an acceptable format.
It was just an expression of frustration about the silly bugs in Microsoft products, especially the 2005 range (how about taking a comment as part of an enumeration, - VB 2005; not letting you move directly to row 0 in a BindingSource.DataSource, you have to MoveLast then MoveFirst - C# 2005; the ComboBox control spewing garbage out until it settles down, thus messing up any text or value changes; the setting the sorted property on a bound combobox sets the value returned to the position in the combobox list instead of the bound data value; The weird error messages often having nothing to do with the type and position of the error..... These are some of the recent value added features.