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