date format and regional settings

Question

Hi all,

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

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

When i try to insert or update with a date of eg. 28/03/2009, I get following errors:

Insert: Error converting data type nvarchar to datetime

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

When I try with a date like 01/03/2009, I get no errors but the date is saved as 03/01/2009, which is the US date format. This is typical behaviour for problems with regional settings. But both are set to Dutch (Belgium).

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

Thanks!
Stijn

Answer 1

You should be inserting data into the database using a DateTime object, not a string. Your client-side code should convert the client's date entry to a DateTime object using the client's regional settings, then the DateTime struct should be added to the parameter that is ultimately sent into the database.

Answer 2

The SQL Instance has it's own locale setting, by default "us_english"

Now, this usually happens if you pushing using varchar rather than native datetime to store data values. If your code/tables use datetime columns and you define parameters as datetime then you won't get errors.

Answer 3

i had this problem too, its something to do with the date format for you SQL server,

i solved it by formatting the date string to be inserted like so

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

hope that helps

Answer 4

All above suggestions are correct but I find if you are adding a datetime as a string/varchar the safest way is in the format 'YYYY-MM-DD'

So eg.

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