How to change the language and date format in SQL Server?

Question

I am using SQL Server 2005. When I execute DBCC USEROPTIONS, I see the language as romana and dateformat as dmy.

I want the language to be us_english and dateformat as mdy.

How can I do this? I was having these settings initially but somehow they got changed to romana.

How to revert back to us_english and date as dmy?

Ironbound

Answer 1

You can reset the language by using the below syntax

set language 'us_english'

In order to view all the languages and their date format try this one

select * from sys.syslanguages

If you need to permanently change the language then

• Go To Security
• Logins
• Right Click on your username and select Properties
• Change the default language to English

This change will persist even after you restart your SSMS

Annadiana

praveenSet it just set the language for current session. When i disconnect from management studio and connect back . Now if i execute DBCC USEROPTIONS , i again see the language as romana instead of us_english – Ironbound

its already english there for all user including my user sa. But still dbcc operation returns as romana – Ironbound

Answer 2

Before

DBCC USEROPTIONS

is returning something like this:

---

To change your or UserLogin configuration run:

USE [master]
GO

ALTER LOGIN [UserLogin] WITH DEFAULT_LANGUAGE=[us_english]
GO

Next - reconnect to SQL (logout -> login).

After

You will get this:

Crisp

To be even more precise you need to logout, close all script windows, login again. Each script window holds it's own connection to the server, so even if you logout you are still able to execute (eg) selects and the useroptions are still unchanged. – Variolite