February 15, 2008 at 4:39 am
I am pulling my hair out trying to get SQL Server 2005 to use British Date format. If iI execute the statement select getdate() within a query window it returns a date in US format - for example 2008-02-15 11:35:13.503
Things I have tried
1. I have changed my login to have a default language of British English
2. Executed EXEC sp_configure 'default language', 23
RECONFIGURE
GO
3. Change the Default language within the Advanced server properties to British English (Bizarrely SQL still shows US English within the General tab)
4. executed SET DATEFORMAT dmy before running a query
running select @@LANGUAGE in my query window returns British
Has anyone experienced this before
February 15, 2008 at 4:47 am
Actually the output is in "odbc canonical with milliseconds" rather than US format.
If you want the date in another format (for display purposes) then you can use the CONVERT function, like so:
select convert(varchar, getdate(), 103)
The third parameter, with a value of 103 is the style, which defines what style you get the date back. All of the other styles are listed in BOL.
February 15, 2008 at 4:54 am
As said SQLZ, with CONVERT or CAST you can format your date correctly as you wish with the code
February 15, 2008 at 5:02 am
Hi ,
Try this :
select CONVERT(VARCHAR,getdate(),103)
Best Regards
Faheem latif
Senior Database Architect
Genie Technologies (Pvt.) Ltd.
February 15, 2008 at 5:06 am
Many thanks for your kind help.
The reason I was experimenting with British date format was because one of our clients reported an issue with our application (that has a SQL backend) where they were experiencing US date formats.
If SQL always stores the date in the same way - what controls how it is presented (I thought the language of the login but that does not seem to have an affect?
Thanks again for your help
February 15, 2008 at 5:08 am
As you've deduced, SQL Server dates are stored in the same way, so the only way to control how it is presented is to change the format of the date, which you can using the convert/cast function or to make that change in the user interface code.
February 15, 2008 at 5:11 am
thanks karl
April 16, 2008 at 7:16 am
Although Datetime Data is stored in the same way regardless of language, there will be problems when inserting 30/06/1980 (English) as oppose to 06/30/1980 (US). Does anyone know how to change the language of SQL Server from English(US) to English(UK) so that UK dates can be inserted ??
April 16, 2008 at 7:28 am
Try:
set language british
That should change your regional settings (for the given connection) to british.
I however tyhink you might need to look at the
SET DATEPART DMY
syntax instead (it will tell what to default the parsing of dates to).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 7:31 am
My server is running on US_English, but this worked:
set language british
create table #DateTest (
Date datetime)
insert into #datetest
select '30/06/1980'
select *
from #datetest
Without the set language command, the rest of the code generates the usual "whoa, I can't understand what you're saying, dude" type error. With that command, it works perfectly.
Since set language changes the language only for that connection, I would think that setting the server language to British would handle the problem on those dates, and create a problem with US dates.
If you have pages that are used in both locations, you might need to set the language in the proc that inserts the data. It can take variables per BOL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply