Scenario
Let’s say that you have a legacy project, or a project for which you want to upgrade the specifications of the environment on which it is deployed (and that the project relies on a Microsoft SQL Server database – MS SQL). Also, the previous MS SQL implementation used the French language, while the current one requires an English version of the database server.
So you have finalized setting up the environment for the application, but when you run the application, you have an SQL query such as:
SELECT * FROM ItemsPurchased WHERE purchaseDate > '2014-25-12'(you want to know all the items purchased after Christmas)
that crashes with the error:
“Conversion of a nvarchar data type to a datetime data type resulted in out-of-range”
What you need to know
* When using the localized version of SQL, a specific date format for the language settings will be set automatically (MS SQL French will set the French date format).
* If no other language is installed, the default installed language pack when performing first MS SQL setup is English (US version), which accepts date formats such as ” MM/DD/YY “. This is OK, if you use the same date format throughout the development process. Otherwise, issues may appear if you distribute your application all around the world, to regions that use other date formats then the common US format.
* For reference, you can see all the available date formats for the MSSQL server by simply running this script “select * from syslanguages”.
Detailing the error
When you run an ASP.NET application, a communication session is created between the server and the desired database (you can set your preferences using various connection strings from the “web.config” file of the application). If the application is set to communicate using French standards with the database, but the database is set to use English, inconsistency errors may occur.
For example, if an interrogation is performed on the database such as the one described in the scenario, the date format “yyyy-dd-mm” can not be directly recognized. If the server accepts the “yyyy-mm-dd” format, it will not accept a different date format, such as “yyyy-dd-mm” (for example, January 23, 2014 formatted as “2014-23-01”). Actually, the server will see the date as an “out of range value”, because it tries to convert “23” to a month value – and there are only 12 months in a year 🙂
You may think that you can modify all the queries to an accepted date format, but think again…as this is not a viable solution. “Why?” you may ask. Here are some reasons:
* It can be very time-consuming if there are more than lets say 10 queries that need altering
* Other parts of your application may require retesting
* When creating new queries you must keep in mind the proper date-format
* What happens if you need to use another SQL version with different language setting?
A quick workaround
The fastest workaround for the MS SQL Server language portability issue (but still, not a perfect solution; for building a proper code, please see the “Best practices” detailed below) is to simply add the preferred language to your connection string:
<add key="MyConnectionString" value="packet size=4096;Password=pass;
Persist Security Info=True; User ID=sa; Initial Catalog=MyDatabase;
Data Source=.\SQL2008;Current Language=English/>
The advantages of this approach are:
* Whenever you decide to change the SQL server to one with a different language, it requires only a small change in the same area
* It does not require code alterations which can cause inconsistencies in the long run
Best practices
* When in doubt what date format to use, always go with the ISO date format! This comes in two “flavors”: YYYYMMDD (no dashes) and YYYY-MM-DDTHH:MM:SS (dashes are optional) where the “T” separates the date from the time. Both these formats have been accepted since the Microsoft SQL Server 2000 version
* Use the English version of Microsoft SQL Server, since it is widely accepted
* I would recommend to always choose English as the database language. If this is not possible, then keep in mind that:
– English is the most widely used language in the world, especially in IT. It can be considered the lingua franca of programming.
– For most programming languages, the instruction set (also the entities’ names etc.) are derived from the English language (for example: “for”, “if”, “table”, “where”). It is common courtesy among developers to keep this standard, and also this ensures that the code is more readable for all programmers.
– Since nowadays most development teams are international, it is difficult to maintain a proper workflow if each developer uses his / her native language. English, as a common language within a team, makes teamwork possible :))
– Imagine your project requires 10 external libraries, and let’s say each of these was written in a different language – how would your source-code look like? Not cool, right?
