Set Locale of a SQL Server 2005

Question

By default the SQL Server comes with the Langauge set to "English (United States)", setting the date format to mm/dd/yy instead of the date format I want it in, which is Australian and has a date format such as dd/mm/yy.

Is there an option in the Server Management Studio / Configuration tools where I can set the locale of the SQL Server, which will prevent the DateTime fields from being formatted in US date format?

If not, how can I convert it when I am using a SQL query such as (forgive me if there is incorrect syntax, I made it up on the spot):

Dim dc As New SqlCommand("INSERT INTO hello VALUES (@Date)", cn)
dc.Parameters.Add(New SqlParameter("Date", System.DateTime.Now))

Many thanks in advance. :)

Answer 1

The DateTime data is stored in a numerical fashion in the database. What exactly is the formatting you're hoping to change? Where do you see it?

Answer 2

have no idea what is the format in "down under"

dd/mm/yyyy hh:mm:ss ?

if yes, that date is the British/French annotation, so all you need to do is:

INSERT INTO hello VALUES convert(datetime, @Date + ' 00:00:00', 103)

or

INSERT INTO hello VALUES convert(datetime, @Date, 103)

if you actually place the time

for more info, check Books online on MSDN to get the correct Code number.

even in Selects I always use this, no matter what's in SQL (cause I tend to use Hosting SQL and there I can't change the formats), like:

SELECT myColumn FROM myTable WHERE myDateField >= convert(datetime, @Date + '00:00:00', 103)

hope it helps

Answer 3

You can set the default language/locale of each user from SQL Management Studio (look under the Security folder).

And override this for a specific connection using the SET LANGUAGE command (or SET DATEFORMAT if you just want to change the date format).

You can also set the default language (used for new users) in SQL Management Studio: right-click on the server, select Properties/Advanced/Default Language.