Date issue

Question

Date issue

Chris-475469

SSCrazy

Points: 2986
More actions
July 3, 2012 at 2:45 am

#258746

I'm sure this is embarassingly obvious, but I have a date issue on various servers which I can't figure out.
If I run an update statement with a date value of 13/05/2012 on one server it runs fine but fails on another with the following error:
Msg 242, Level 16, State 3, Line 1
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.
On the other server I have to use 2012-05-13.
The servers in question appear to have the same settings on the server and instance level.
Any ideas?
July 3, 2012 at 2:56 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113499 More actions · Answer 1

Looks like a dateformat issue, maybe on one server your login is set to English (MDY) format and on the other British English (DMY) format.

So on the MDY server, your trying to insert a month value of 13 which doesnt exist, where as when you do 2012-05-13 SQL knows its in YMD format.

When it comes to dates it is always recommended to format your dates in an ISO standard which will not encounter issues like this.

Chris-475469 SSCrazy Points: 2986 More actions · Answer 2

Thanks for the info.

I can now see that the date format is different. The server with the issue has a format of mdy where as the one that is ok has dmy.

How can I change this?

Basit Farooq Ten Centuries Points: 1151 More actions · Answer 3

Specify the date format at the start of the script as follow:

SET DATEFORMAT DMY;

.....

...

..

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Chris-475469 SSCrazy Points: 2986 More actions · Answer 4

I'm afraid I need to change it at the instance level if possible.

The SQL code is wrapped in an app I don't have access to.

Ant-Green SSC Guru Points: 113499 More actions · Answer 5

Change the logins default language to British, or change the properties of the server to British (right click server --> properties --> advanced), on both be sure that this doesnt break other things which might use a dateformat of MDY first though.

And as a point to remember use ISO date formats, not locale date formats.

Chris-475469 SSCrazy Points: 2986 More actions · Answer 6

Anthony - The langauge for the instance is already set to British English, as it is on the server that doesn't have the issue.

That is what confused me. I can't see how they can have different date formats if they have the same language setting. Unless something was run on the existing server sometime ago.

Although I can see the name is British on the server that's ok but us_english on the other.

Ant-Green SSC Guru Points: 113499 More actions · Answer 7

Ant-Green

SSC Guru

Points: 113499

July 3, 2012 at 3:35 am

#1508097

whats the language of the login set to?

Chris-475469 SSCrazy Points: 2986 More actions · Answer 8

The login is set to British English.

I just added a note to my previous post to say that if I run sp_helplanguage @language = @@LANGUAGE, the name of the language on the server with the issue is us_english whereas it's British on the other server.

I'm sure I can use sp_configure to change this?

Ant-Green SSC Guru Points: 113499 More actions · Answer 9

sp_configure will only change the default language for new logins created not at the server level.

http://coolkidsdoit.wordpress.com/2009/01/28/changing-the-default-languagelocale-sql-2005/

Relates to 2005 but just substitute \90\ with \100

Disclaimer - not tried, nor do I personally want to try this, just ensure you backup your registry before doing anything like this.

Chris-475469 SSCrazy Points: 2986 More actions · Answer 10

Should I not be able to use:

EXEC sp_configure 'default language', 23

RECONFIGURE WITH OVERRIDE

This doesn't appear to work though.

Ant-Green SSC Guru Points: 113499 More actions · Answer 11

no as I said in my last post, that will only change the default language for any new logins which are created, not the actual instance.

Chris-475469 SSCrazy Points: 2986 More actions · Answer 12

Ok I see what you mean now.

At the moment it looks like a re-install.

Ant-Green SSC Guru Points: 113499 More actions · Answer 13

Or change the registry, but as I say, I dont personally recommend doing that, but for something so small might be better than an uninstall reinstall. Might save you a couple hours work.