Description
The behavior of DATETIME or SMALLDATETIME types of data can be influenced by the client, server, and/or the operating system settings. This KB article describes how to use ApexSQL Data Diff in situations where these settings must be considered
Solution
To illustrate, three databases located in three different countries will be used. The goal is to synchronize the data between any two of these databases. The main database is hosted on a SQL Server instance with the US English settings; the other two databases with the French and German settings.
The script below creates three databases: General, French, and German. Each database contains one table named T. The table T contains only one DATETIME column named TheDate:
CREATE DATABASE GENERAL
COLLATE LATIN1_GENERAL_CI_AI
GO
CREATE DATABASE FRENCH
COLLATE FRENCH_CS_AI
GO
CREATE DATABASE GERMAN
COLLATE GERMAN_PHONEBOOK_CS_AI
GO
USE GENERAL
CREATE TABLE T
(
THEDATE DATETIME
)
GO
USE FRENCH
CREATE TABLE T
(
THEDATE DATETIME
)
GO
USE GERMAN
CREATE TABLE T
(
THEDATE DATETIME
)
GO
Common problems with temporal data when working in international environments
USA, France, and Germany all use a different date format. To illustrate what happens when data is inserted into the databases above, the Set language command is used
To insert data into the General database using the French date format DD/MM/YYYY, the script below is executed:
USE GENERAL
SET LANGUAGE ENGLISH
INSERT INTO T VALUES(’23/08/2007′)
CHANGED LANGUAGE SETTING TO US_ENGLISH.
MSG 242, LEVEL 16, STATE 3, LINE 3
THE CONVERSION OF A CHAR DATA TYPE TO A DATETIME DATA TYPE RESULTED IN AN OUT–OF–RANGE
DATETIME VALUE.
THE STATEMENT HAS BEEN TERMINATED.
Due to the difference in language settings, the insertion fails. SQL Server cannot resolve the string ’23/08/2007′ to a valid date format.
To insert the same data into the German database, the following script is executed:
USE GERMAN
SET LANGUAGE GERMAN
INSERT INTO T VALUES(’23/08/2007′)
DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT.
(1 ROW(S) AFFECTED)
The data is inserted successfully. The same thing is done with the French database using the script below:
USE FRENCH
SET LANGUAGE FRENCH
INSERT INTO T VALUES(’23/08/2007′)
LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS.
(1 ROW(S) AFFECTED)
The insertion into the French database is successful as well. The results would be the same if there were an attempt to insert data with the German date format DD.MM.YYYY into the three sample databases; the insertion would fail on the General database whereas it’d be successful on the other two.
On the other hand, if there was an attempt to insert data that’s in the US date format MM/DD/YYYY into all three databases, only the insertion into the General database would be successful. The two other INSERT statements cannot be committed successfully and would raise a conversion error.
Using the SQL Server’s CONVERT statement to apply the correct date format style before performing the insertion, resolves these insertion errors. In the next section, see how to synchronize temporal data in international environments that are similar to the given example by using ApexSQL Data Diff and without the additional “conversion” step.
Using ApexSQL Data Diff to synchronize temporal data when working in international environments
Example 1:
In this example, the French database has one row that’s not in the General database. To synchronize the General database with the French database, ApexSQL Data Diff generates the synchronization script below:
USE GENERAL
SET LANGUAGE ENGLISH
SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, ‘20070823 00:00:00.000’)
SET IDENTITY_INSERT [DBO].[T] OFF
CHANGED LANGUAGE SETTING TO US_ENGLISH.
(1 ROW(S) AFFECTED)
Example 2:
In this example, the German database has one row that’s not in the French database. To synchronize the French database with the German database, ApexSQL Data Diff generates the following synchronization script:
USE FRENCH
SET LANGUAGE FRENCH
SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, ‘20070823 00:00:00.000’)
SET IDENTITY_INSERT [DBO].[T] OFF
LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS.
(1 ROW(S) AFFECTED)
Example 3:
In this example, the General database has one row that’s not in the German database. To synchronize the German database with the General database, ApexSQL Data Diff generates the following synchronization script:
USE GERMAN
SET LANGUAGE GERMAN
SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, ‘20070823 00:00:00.000’)
SET IDENTITY_INSERT [DBO].[T] OFF
DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT.
(1 ROW(S) AFFECTED)
All of the scripts shown above complete successfully. By default, ApexSQL Data Diff uses the language-neutral date format settings. This default setting can be modified under the New project window by selecting the Options tab in the left panel, and then locating Apply date format under the Synchronization options section.
The Apply date format option uses the non-separated date format by default:
The most commonly used formats are also readily available:
To add a format that’s not in the list or to use one that already is, check the Apply date format option, select (or enter) the desired format, and click the Save as my defaults button. The selected format will then be used by ApexSQL Data Diff on all (SMALL)DATETIME data type columns encountered during the scripting process.
For example, if the US date format is used:
The script output would be:
SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, ’08/23/2007 00:00:00.000′)
SET IDENTITY_INSERT [DBO].[T] OFF
Last updated
August 1, 2014
