October 29, 2009 at 4:05 am
Hello,
Our SQL server was originally installed as English (United States) and due to various reasons we didn't immediately change it to English (United Kingdom).
Because of a license key issue with a third party piece of software, we had to change the locale of the server to English (United Kingdom).
I have ran:
EXEC sp_configure 'default language', 23
RECONFIGURE
and
SET Language British
Additonally, I have also ensured all SQL Server logins are using English (British) as their default language.
We have an SSIS package which does various things with some import data, including some date functions in VB and T-SQL. I have converted this to "do its stuff" in a British way, i.e. dd/mm/yyyy. If I am logged on to the SQL server and run the package in Visual Studio, it works fine. If I schedule the package in a job, the package fails, complaining of not being able to convert String to Date - the date in question being 28/10/2009 - a valid British date, but obviously not a valid US date.
Do I need to restart SQL Server services or the server?
Thanks,
Stewart
October 29, 2009 at 5:05 am
You shouldn’t rely in your code on the configuration or setting of the server/session. You should make sure that your code will run regardless of the language and date format that are configured to the database or the login that runs the code. There are 2 ways of doing so. The first way is to use a format that will be understood by the server in the same way regardless of the language and date format setting. One of this formats that will always be interpreted the same way is the ISO format – a string with the fallowing structure ‘yyyymmdd’. For example October 29 2009 will be written as ‘20091029’. The second way is to specify the date format that you are using with the SET DATEFORMAT statement. In your case since you want to use the British format, you can issue the following statement:
SET DATEFORMAT dmy
After this statement your session will be able to work with the British format.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 29, 2009 at 9:44 am
I'd second the idea of using ISO date formats - it's what I usually do as I work on en-gb servers all the time. However, another possible solution is to add a CONVERT statement to your date strings, thus:
CONVERT(datetime, '28/10/2009', 103)
Whichever works for you, really, but as Adi says, don't trust the server to guess right for you..
Cheers,
Duncan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply