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
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.