Datetime issues for different regions in SQL/CMT Application

Database exception

Eg: “The conversion of a varchar data type to a datetime data type resulted in an out-of –range value.The statement has been terminated.”

This is due to unmatched date-time formats between current application running system and the database.

For Eg: Your application running on system which is following Spanish (Spain) with “dd/MM/yyyy”  date format  and the database that your application storing the data following US-English with “M/d/yyyy” date format. In that scenario you will get this kind of exception.

To avoid this exception, we must set both date formats are of same type.

How to Change Region Language, Date & Time Settings

Here we are showing how to change region settings in Operating System and how to set default language and datetime format settings for SQL server. Here we are showing an example for country “Spain”.

In Operating System:

1. Click on Start Menu ButtonàGo to  Control Panel
2. Click on “Clock, Language, and Region” optionàClick on “Region and Language”
3. Click on Formats tabàChange the format from “English(United States)” to “Spanish (Spain)” and Set First day of week to “lunes”.
4. Click on Location tabàChange Current location from United States to SpainàClick on “Apply” and finally click on “OK” button.
5. Now click on the time shown on taskbaràclick on “Change date and time settings”àclick on “Change time zone”àSelect the concerned time zone for “Spain” Location.

For “Spain” choose “(UTC +01:00) Brussels, Copenhagen, Madrid, Paris” timezone.

This is all from Operating System side. Now let us see  how to set default language  and its date time formats from SQL Server side.

In SQL Server Management Studio:

1. Run  SQL Server Management Studio as an Administrator or we may run it as a normal user.

1. After  Logon  into Sql ServeràFrom the Standard bar, click New Queryà

Copy and paste the following command into the query window and click Execute.

DBCC USEROPTIONS

1. After executing the command, query window will display current language and datetime formats.

In the result we can notice, “language “ option value is “us_english” and “dateformat” option value is “mdy”.

Change Default Language for SQL Server

We can set default language for SQL server in two ways:

1. i.            Using SQL Server Management Studio
2. ii.            Using Transact-SQL

i). Using SQL Server Management Studio

To configure the default language option:

• Go to ObjectExplorerà Right click on your database server nameàPropertiesàServer Propertieswindow wil be opened.
• Click on AdvancedàChange DefaultLanguage from “English” to “Spanish “à Click on OK button.

ii). Using Transact-SQL

To configure the default language option:

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.

This example shows how to use sp_configure to configure the default language option to Spanish

USE master ;

GO

EXEC sp_configure‘default language‘, 5 ;

GO

RECONFIGURE;

GO

Note: In the above example, “5” is the language- id for  Spanish& “master” is present default database in SQL.(Instead of  “master”, we can use our currently using database name)

We have to mention respective language id as per our requirement. For language ids, see the below table.

╔════════╦═════════════════════╗

║ LANGID ║        ALIAS        ║

╠════════╬═════════════════════╣

║      0 ║ English             ║

║      1 ║ German              ║

║      2 ║ French              ║

║      3 ║ Japanese            ║

║      4 ║ Danish              ║

║      5 ║ Spanish             ║

║      6 ║ Italian             ║

║      7 ║ Dutch               ║

║      8 ║ Norwegian           ║

║      9 ║ Portuguese          ║

║     10 ║ Finnish             ║

║     11 ║ Swedish             ║

║     12 ║ Czech               ║

║     13 ║ Hungarian           ║

║     14 ║ Polish              ║

║     15 ║ Romanian            ║

║     16 ║ Croatian            ║

║     17 ║ Slovak              ║

║     18 ║ Slovenian           ║

║     19 ║ Greek               ║

║     20 ║ Bulgarian           ║

║     21 ║ Russian             ║

║     22 ║ Turkish             ║

║     23 ║ British English     ║

║     24 ║ Estonian            ║

║     25 ║ Latvian             ║

║     26 ║ Lithuanian          ║

║     27 ║ Brazilian           ║

║     28 ║ Traditional Chinese ║

║     29 ║ Korean              ║

║     30 ║ Simplified Chinese  ║

║     31 ║ Arabic              ║

║     32 ║ Thai                ║

║     33 ║ Bokmål              ║

╚════════╩═════════════════════╝

• We can get the above language ids list by executing the following command in query window

— Languages in SQL Server 2008 with dateformat

SELECT LanguageID = langid,

name,

alias,

dateformat

FROM sys.syslanguages

ORDER BY langid

GO

Change Default Language for SQL Logins

Now we have to set default language for SQL Logins, we can do this in two ways:

1. i.            Using SQL Server Management Studio
2. ii.            Using Transact-SQL

i). Using SQL Server Management Studio

• Go to ObjectExploreràExpand SecurityàExpand LoginsàSelect  “sa” (which is a Login ID/ UserName)àRight Click on “sa”  and click on Properties àLogin Properties window will be opened.
• Select General tabàChange Default language from “ English” to “Spanish”àFinally Click on OK button.

Note: Here we can also set our currently using database as a default database, by default it is master.

ii). Using Transact-SQL

Copy and paste the following example into the query window and click Execute.

use master

EXEC sp_defaultlanguage’sa’,’Spanish’

GO

Note: In the above example, “master” is the default database, & “Spanish” is the language that we want to set as default language. (We can use our current database name instead of “master”).

–this step is optional

• To set our currently using database as a default database, Copy and paste the following example into the query window and click Execute.

EXEC sp_defaultdb’sa’,’CMT_DB’

In the above example, “sa” is the login-id/username & “CMT_DB” is the database that we want to set as a default database.

• Now once Close and Restart the SQL Server Management Studio as an Administrator or as a normal user to take configuration changes into account.

Run the following command in Query window:

DBCC USEROPTIONS

• After executing the command, query window will display current language and date formats.
• We can notice that language and date formats are changed to Spanish Region.
• In the result we can notice, “language “ option value is “Español” and “dateformat” option value is “dmy”.