Software Business Partners: .NET and C++ outsourcing company from Romania
 

How to deal with MS SQL language portability issue in ASP.NET - Part 1

Apr 10, 2014 by Alexandra

EFXZZYRGQ7PB
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?

Intrigued? Stay tuned as I'm coming up with a solution in my next post.

Update: the solution is here in "How to deal with MSSQL language portability issue in ASP.NET - Part 2"!


Tags: Database  How To  Microsoft  SQL 


Comments


none commented on 4/18/2014 10:18:18 AM

Duh,
use parameterized query. Using string literals and concatenating strings - you are leaving yourself open for SQL injection attacks.
If you absolutely insist in using string literals use yyyymmdd format.
     

Alexandra commented on 4/23/2014 11:12:29 AM

Hi,

Thank you for your comment.

First of all, the string literals that you are referring, from the SQL query example, were used for educational purposes, in order to better reflect the issue at hand - the date format incompatibility. Of course, string literals should be avoided in any situation, due to the fact that they are prone to SQL injection.

Also, there is no "generally accepted" date format. Even "yyyymmdd" could generate errors (as described in the post), when used with improper SQL language formatting.

Please see my solution to how to deal with MSSQL language portability issue in ASP.NET in Part 2 of the post:

http://www.sbp-romania.com/Blog/2014/04/15/how-to-deal-with-ms-sql-language-portability-issue-in-aspnet-part-2.aspx

     

RobIII commented on 4/23/2014 1:42:36 PM

> "Also, there is no "generally accepted" date format."
Yes there is. It's called ISO 8601 and has been for years (see https://en.wikipedia.org/wiki/ISO_8601).

Also relevant: https://xkcd.com/1179/

I have no idea what caused you to come up with, let alone even try, yyyyddmm. Also see: http://i.imgur.com/7tyS3lg.jpg
     

Alexandra commented on 4/28/2014 12:03:29 PM

Hi,

Thank you for your comment and for the resources :)

I agree that the ISO standardized format is the format to go with (as I've stated in Part 2 of the post), however sometimes, even this can generate errors.

For example: if your SQL Server is set to use another date format, the ISO 8601 may generate errors.

So my post comes with a solution for a particular case, and not does not contradict the general approach.

     

none commented on 4/24/2014 12:18:01 PM

Alexandra/Roblll,

YYYMMDD doesn't generate any errors, is part of ISO8601 (along with YYYY-MM-DD, and is preferable as datetime formatting routine as unless you are explicit about it, most datetime formatting routine replace - with the localized date separator.

The way Alexandra is changing the current culture to English is problematic as it changes a whole lot of things - for example start of day and other stuffs.
     

Your Comment:






Blog Home   SBP Home
RSS Feed       Contact








 Blog Archives  |  Terms of Use  |  Privacy Policy
© 2017 SBP Romania. All rights reserved.