May 7, 2002 at 2:42 am
Hi. I have 2 SQL 2000 Servers, one live and the other dev. When I run the ff query on both servers; SELECT TOP 2 * FROM MY_TABLE WHERE MYDATE = '31/03/2002' (MYDATE = SmalldateTime(4)).
I get the right result set on live but I get this error on dev; "Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
If I now swap the date and month around as in; "WHERE MYDATE = '03/31/2002', I now get the correct result on dev and get the same error on live.
The regional settings for dates on both machines are identical.
Can anyone please help.
Tnx in advance.
May 7, 2002 at 6:05 am
When you say the regional settings are identical do you mean for Windows or SQL? SQL Has a language setting as well that handles the format internal. Try ths follwoing to make sure both are using the same laguange.
SELECT @@LANGUAGE AS 'Language Name'
If not then try the follwong from BOL
quote:
SET LANGUAGESpecifies the language environment for the session. The session language determines the datetime formats and system messages.
Syntax
SET LANGUAGE { [ N ] 'language' | @language_var }
Arguments
[N]'language' | @language_var
Is the name of the language as stored in syslanguages. This argument can be either Unicode or DBCS converted to Unicode. To specify a language in Unicode, use N'language'. If specified as a variable, the variable must be sysname.
Remarks
The setting of SET LANGUAGE is set at execute or run time and not at parse time.
Permissions
SET LANGUAGE permissions default to all users.
before your query to see if makes a difference. If so then you need to change the SQL servers configuration.
From BOL
quote:
How to set the default language (Enterprise Manager)To set the default language
Expand a server group.
Right-click a server, and then click Properties.
Click the Server Settings tab.
In the Default language for user box, choose the language in which Microsoft® SQL Server™ should display system messages.
The default language is English.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply