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 ) GOUSA, 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.
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:
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:
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:
