question about conversion

[David]

Hi,

We use an english version windows server.
The regional setings are set to French (France).
Asp.Net renders and interprets the date correctly (dd/mm/yyyy). So does Sql
Server (date is saved in dd/mm/yyy format in datetime field).

When inputting a date into a textbox (dd/mm/yyyy) which must be inserted in
Sql server, there comes an error "string cannot be converted to date ...".
By applying <globalization culture="fr-BE" uiCulture="fr-BE" />, the problem
is solved.

My question is: why does this happen? The two parties (ASP.NET and Sql
server) understand each other and understand the right date format, no? So,
can the conversion process be considered as a third party between ASP.NET
and Sql Server, which always apply the english date format (mm/dd/yyyy)?

Why this confusing distinction between the regional settings and culture?

Thanks
David

 

[Patrice]

Dates (even in SQL Server) are always stored *internally* the same way. The
problem is that as soon as a date is written down each country have its own
specific text representation.

This is a classic error when you just stuff the date inside an SQL string in
which case you depend on regional settings. Instead use the ISO format
(YYYYMMAA that is always read correctly regardless of regional settings) or
even better use parameters so that this is handled for you (it could solve
also a posisble problems for the decimal point as we use , in France but .
in other countries etc...).