SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 inserting date		  Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

awp
Starting Member

13 Posts
Posted - 09/20/2006 :  17:17:43  Show Profile
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)

USA
6997 Posts
Posted - 09/20/2006 :  17:32:37  Show Profile
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
Go to Top of Page

awp
Starting Member

13 Posts
Posted - 09/21/2006 :  18:18:27  Show Profile
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 09/21/2006 :  19:29:30  Show Profile
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:
Syntax
sp_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
Edited by - Michael Valentine Jones on 09/21/2006 19:30:24
Go to Top of Page

awp
Starting Member

13 Posts
Posted - 09/22/2006 :  05:13:21  Show Profile
Thank you very much! That cleared a lot and make sense to what was happend to me!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000