Does changing Regional Settings affect SQL Server?

Question

Post reply

Does changing Regional Settings affect SQL Server?

Meto

SSC-Addicted

Points: 433
More actions
March 2, 2004 at 12:51 pm

#65541

We have SQL Server 2000 running on a server where the regional settings are set to United States although we are in New Zealand. As a result I use the command SET DATEFORMAT dmy before I issue any sql commands. This works fine in all cases except one. I have a DTS package that is used to create a data warehouse. Even though I use SET DATEFORMAT dmy in both the Insert and Update queries I am still getting dates entered with the day and month reversed. If I run this DTS package from Enterprise Manager on my PC it works correctly. But when DTS runs once a month as a scheduled job it does not work correctly.
So, I am wondering what affect changing the Regional Settings to New Zealand will have. I would appreciate hearing from anyone having experience with this.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Angela But Hall of Fame Points: 3848 More actions · Answer 1

Hi Meto,

Yes. Regional Settings DO affect SQL Server.

You should always change your Regional Settings before installing SQL Server as SQL Server looks at these settings upon installation. As far as I know you will have to reinstall SQL Server AFTER changing your Regional Settings to get around it, unless you want to continue hardcoding the dateformat in your stored procs, etc.

The reason you are seeing the behaviour you described is the DTS package when scheduled as a job runs on the Server, when you execute from your EM it runs on your local machine. I am betting that on your machine the Regional Settings are set to NZ.

I'm in Australia, and every SQL Server install I do I always change the Regional Settings before doing anything else.

Cheers,

Angela

wz700 SSCertifiable Points: 7133 More actions · Answer 2

You do not have to change regional settings to solve the problem.

The accepted dateformat depends on user default language. Simply change user default language to 'British' (dmy) should do.

sp_helplanguage lists all available languages and their date format.

@@language is your default language. It might be us_english in your case. I believe regional settings on sql server installation decides the value.

Use sp_defaultlanguage to change login default language.

Change regional settings after SQL Server setup may not change @@language value.

Angela But Hall of Fame Points: 3848 More actions · Answer 3

wz700,

While not disputing changing @@language may determine language and datetime formats, I have always changed it in Regional Settings so that it is consistent across the server.

Interestingly, upon checking my servers @@language = US_English but because my Regional Settings are English(Australia) this is the format my dates are using NOT the US format.

Angela

wz700 SSCertifiable Points: 7133 More actions · Answer 4

The following tests may illustrate my point, the first one should fail while the second should be ok.

SET LANGUAGE us_english

declare @test-2 table (c1 datetime)

insert @test-2 (c1) values('13-1-2004')

select * from @test-2

go

SET LANGUAGE british

declare @test-2 table (c1 datetime)

insert @test-2 (c1) values('13-1-2004')

select * from @test-2

Angela But Hall of Fame Points: 3848 More actions · Answer 5

If the default language is US_English and you want to change it to British or whatever, using @@Language would have to be hardcoded into your stored procs everytime. @@Language only sets the language for the duration of a current session.

It will not change it for all databases on the SQL Server.

I think Meto was trying to get around hardcoding the @@language/SET dateformat so the SQL Server automactically used date formats as dd/mm/yyyy rather than mm/dd/yyyy.

Regarding sp_defaultlanguage From BOL :

"..Changing the server default language does not change the default language for existing logins.."

So if you were going to use this option you would have to set the language for all existing logins as well. In Meto's case this will mean changing it for the login that is running the job.

Meto, maybe you could use this as a workaround for your problem and look at Regional Settings next time you install SQL Server, so you don't continually run into this problem.

Meto SSC-Addicted Points: 433 More actions · Answer 6

Thanks for your responses. Next time we install SQL Server I will ensure that the regional settings are correct before installation. Until then I will continue to use the workarounds.