Date format US/UK

Hi

I have installed SQL server 2005 developer edition.When trying to insert a date in the UK format '16/04/2007' I get the following error...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

if I change the date to be inserted to an American format then I am able to insert it.

I don't want to change the sql as this is something that has always worked - but not with my local install of sql server.

other info that may be of relevance...

My regional settings are all set to UK and this has been applied to the default profile
The server & database collation is Latin1_General_CI_AS
If I attach this database to another instance of sql server installed on a another machine then the insert works.
My pc is a Dell XP Professional SP2

any/all help is appreciated!

chris

Before your insert query add the following statement

SET DATEFORMAT dmy

Insert .....|||

Hi

Thanks for the post

I am thinking maybe I have posted in the wrong forum?

what I don't want to do is write more TSQL, rather I would like to know

what is causing the date to be inserted in the US format so that I can change the setting.

thanks

chris

|||Yes you are absolutly correct.. |||

Rather than doing an implicit conversion by inserting the text into a datetime field use an explicit conversion (onto which you can place a style identifier.

Rather than '16/04/2007' Use CONVERT(datetime, '16/04/2007', 103)|||

Thanks for the posts

I have posted this in the database engine forum so please dont post anymore to this thread.

regards

chris

Date format UK/US

Hi

I have installed SQL server 2005 developer edition.When trying to insert a date in the UK format '16/04/2007' I get the following error...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

if I change the date to be inserted to an American format then I am able to insert it.

I don't want to change the sql as this is something that has always worked - but not with my local install of sql server.

other info that may be of relevance...

My regional settings are all set to UK and this has been applied to the default profile
The server & database collation is Latin1_General_CI_AS
If I attach this database to another instance of sql server installed on a another machine then the insert works.
My pc is a Dell XP Professional SP2

What I don't want to do is write more TSQL but change settings that can alter the behaviour.

any/all help is appreciated!

chris

Chris,

See your other post in Transact-SQL for responses.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Thanks for your reply

If you read my other post I realise that I have posted in the wrong forum and I clearly state that! as I do not want a TSQL solution.

hence I posted here afterwards

but thank you for your help

|||

Oh and this has not been answered so all constructive help is still appreciated

thanks

chris

|||

Ok one of my colleagues has fixed it.

It turns out that us guys in England don't speak English - we speak British English?

The default language on the Login had to be set to British English.

thanks

chris

Date field cannot be null!

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

With this it looks like your table design has the field set not to allow nulls. You will have to alter the table definition to allow nulls for that field. This can be done in raw tsql, or using the table design views in either of the management studio tools.

|||

I went into DataSetDesigner and in Properties I set AllowDbNull to True.

But, when I tried to change the NullValue property from "Throw Exception" to "Empty" or "Nothing" (there are only 3 choices) it said "For columns not defined as System.String, the only valid value is (Throw exception)".

|||

Given that you've just allowed nulls, the value of the NullValue property is irrelevant, as the exception should never be raised.

|||

The following exception occurred in the DataGridView:

System.Data.NoNullAllowedException: Column 'BirthDate' does not allow nulls

I set 'AllowDbNull' to True in the properties field of the DataSet Designer, replied 'Yes' to 'Save Changes', but, when I go back in, 'AllowDbNull' is back to False.

|||

ok - I've done some research on this (I'm a SQL guy, not a VS guy) and it looks like there may be a bug in the Dataset Designer for non-string columns. I found what looks like a workaround here: http://www.codeproject.com/useritems/Bug_fixed_in_DataDesigner.asp

Hope this helps