Date Format and Regional Settings

Question

I am using MS SQL 2000, VS2008, MVC and C #.

I am trying to insert and update some data using stored procedures. Some columns are of type datetime. The regional settings on the server and on the client are set to Dutch (Belgium). This means that the default date format is dd / mm / yyyy.

When I try to insert or update a date, for example. 03/28/2009, I get the following errors:

Insert: Error converting nvarchar to datetime data type

Update: Converting char data type to datetime data type results in datetime time out of range

When I try to specify the date 01/03/2009, I get no errors, but the date is saved as 03/01/2009, which is the date format in the USA. This is typical behavior for problems with regional settings. But both are installed in Dutch (Belgium).

Why does it save dates in US format?
What am I missing here?

Thanks!
Stein

Answer 1

You must insert data into the database using a DateTime object, not a string. Your client-side code should convert the client date record to a DateTime object using the regional settings of the client, then add the DateTime structure to the parameter that is ultimately sent to the database.

Answer 2

The SQL instance has its own locale parameter, the default is "us_english"

Now this usually happens if you click using varchar instead of your own datetime to store data values. If your code / tables use datetime columns and you define the parameters as datetime, you will not get errors.

Answer 3

I also had this problem, something like a date format for a SQL server,

I solved this by formatting the date string to be inserted like this

DateTime.Now.ToString("MM/dd/yyyy HH:mm") 

hope that helps

Answer 4

All the above suggestions are correct, but I believe that if you add a date-time as the / varchar string, the safest way is in the format "YYYY-MM-DD"

So for example.

  Update MyTable Set MyDate = '2010-03-01'