Previously I had a problem with my ASP .NET program which it's returned date
format was not the appropriate one. As I've mentioned in the "Date
Format in ASP .NET Web Application Served by IIS 6" post you can adjust your
application date format with "globalization" element in "web.config",
but I had
another problem, although I changed the date format in my application but I had
a problem when I wanted to submit a query to sql server and I got the following
error when my query had a date in it's code:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated."
After searching a bit I found that the cause of this problem is the sql
server date format. Although I had changed my application culture to "en-US" but
my sql server default language was different. To find out your sql server
default language, you can run the following query:
select name, alias,
value from master..sysconfigures
where comment =
The above query shows your sql server default language. But your database
user might have another language. If you select "default" for user language
during user creation, then
the language of user will be set to the database default language, but you can
select another language for your database user. To find out what is the default
language for your database user, you should login with that user to the database
and run the following command:
the above query shows you the database user default language and dataformat.
After you have found that the user date format is not appropriate for you,
you can change it by the following query:
The above query changes the language of specified user to "english".
If you want to get information about a language properties in sql server
(such as it's date format, month names, ...) you can run the following
or you can specify your desired language as follows:
So after changing my application culture to "en-US" using the globalization
element in web.config and changing my sql server user language to "us_english"
using the above query, all my date time problem were resolved. :)
But there is a note about date format, if you use standard ISO date format that
is "YYYYMMDD HH:MM:SS.mmm", then you will be independent of the language setting
the sql database is configured in and everything works without doubt!
To read more about this subject visit the following pages: