Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
awp
Starting Member
13 Posts |
 Posted - 2006-09-20 : 17:17:43
|
| I am trying to insert a date value into a table with smallDateTime field. My database has the language setting of English (united states) and the computer where the database is running has the regional setting (language) of English (united kingdom). I am using the following statement which is dynamically genertaed by ASP to insert a record into a table (I insert the date as month/day/year as the Database has United states language setting).Insert into tbl_PAS_Incidents (incident_desc, incident_action, incident_date, upn, current_year) values ('test','test','9/20/2006','W881264796031','10') This query runs without a problem only for certain users who have admin rights for the domain in which our the SQL server resides. When other users who have less rights in the domain use exactly the same data and statement, the insert fails with error below.Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.Remarkably, when I change date string to day/month/year (as United kingdom), the statement runs sucessfully for the less right users but throws the same error for admin right users. Can anyone explains why this is happenning? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-20 : 17:32:37
|
The users have different default language settings.To prevent the problem from happening, insert in format YYYYMMDD:Insert into tbl_PAS_Incidents (incident_desc, incident_action, incident_date, upn, current_year)values ('test','test','20060929','W881264796031','10') CODO ERGO SUM |
 |
|
|
awp
Starting Member
13 Posts |
Posted - 2006-09-21 : 18:18:27
|
| Thanks, it now works for all users. however, what puzzels me is why my original statement didn't work for different users with same regional settings. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-21 : 19:29:30
|
| The default language is defined on a login basis, and the default language defines the default date format. As I said before, the users have different default language settings.The format I gave you is the universal format you should always use when converting character strings to datetime. SQL Server will always convert it correctly.From SQL Server Books Online:Syntaxsp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ] [@deflanguage =] 'language'Is the default language assigned when a user logs on to SQL Server. language is sysname, with a default of NULL. If language is not specified, language is set to the server's current default language (defined by the sp_configure configuration variable default language). Changing the server's default language does not change the default language for existing logins. language remains the same as the default language used when the login was added.CODO ERGO SUM |
|
|
|
awp
Starting Member
13 Posts |
Posted - 2006-09-22 : 05:13:21
|
| Thank you very much! That cleared a lot and make sense to what was happend to me! |
|
|
|
|
|
|
|
|
inserting date