How to change the language and date format in SQL

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?

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

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: