How to change the Date format from US to UK

[Jeremy74]

As the title sugests how do you change the date format in SQL server.

Cheers

Jeremy

 

[grahams]

Assuming you're talking about changing the date format in a query rather than changing the server setting, use CONVERT with the appropriate format number, e.g.

select myUKDate = CONVERT(datetime, myUSDate, 103)

See CONVERT in BOL.

HTH

Graham

 

[Jeremy74]

Hi Graham

It's at the server level.

 

[grahams]

In that case I think you'd have to change the Locale on teh server itself - which, if you have any data in SQL Server already, I wouldn't do. Why do you want to do this?

 

[Jeremy74]

We have just got a new server and installed SQL on to it. The website we are running on the system was running okay until it hit the 13th. The date format in the database is MDY and we think this is the problem. We are getting an "invalid date error on the site".

Any advice appreciated

Cheers

Jeremy

 

[grahams]

Well, yes - as you say the problem has become apparent because there are only 12 months in the year. But I'm afraid any date comparisons in your application that contained an ambiguous date will have been wrong the whole time.

I think I'm right in saying that by default SQL Server installs with the locale set to US-English and therefore uses an MDY date format. To avoid these sorts of problems any queries used by an application should control the format of dates whether in comparisons or for display purposes. Thus dates in comparisons should always be converted to a like format, and dates for display (especially in a Web app) should be converted to a varchar version in the desired format to avoid any problems caused by the client's locale setting.

I would suggest, therefore, that you find the errant queries used by your application and correct them. If you already have data in the database(s) then changing the database and/or server settings will cause havoc you wouldn't want to contemplate...

 

[JamesLean]

Jeremy, SQL Server only ever stores dates in one format and one format only.

Terry Broadbent has recently written an FAQ on this subject which you should find useful: faq183-3007

 

[Jeremy74]

What I don't understand is we are importing the database and website from another server and it's working okay on that server.

Any ideas

 

[grahams]

You could check the locale settings (look at the SQL Server properties in enterprise manager) to see whether one is set to US English (the default) and the other is set to British English - this will affect the default date format (in presentation...) - but it won't really solve the problem I'm afraid.

 

[tlbroadbent]

This link may be even more useful than the FAQ.

Handling Dates in SQL Server

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.