Error with dates in SQL Server

[ANDYCHARGER]

Im doing an insert on my tables in SQL server which is passing dates from a text box on my web page. Im formatting the dates so the 1st of August 2004 is being passed from the page as "01/08/2004 00:00:00".
The trouble is, once it is commited in the DB, the date shows in the column as "08/01/2004"

Is there some setting im missing in SQL server that sets it to the correct regional format?

Please help!!!! Argh!

 

[nigelrivett]

You should always pass dates in the format yyyymmdd hh:mm:ss as this is unambiguous.
The way you are passing it may cause problems due to client and server settings and may not work on a different client.

If the column in the database is datetime then it is held as a number - the value you are seeing is just the way you have set up the system to convert it to a string for presentation.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ANDYCHARGER]

Thanks.

Instead, I have changed the regional user settings to British English, restarted SQL Server and it has fixed my issue!

Andy

 

[SQLBill]

You can always start your script with SET DATEFORMAT. That will let SQL Server know what the format is:

SET DATEFORMAT dmy
SET DATEFORMAT mdy

Refer to the BOL for more information on DATEFORMAT.

-SQLBill