Symptom
If you create a SQL Native Client connection in PowerBuilder to access a MSSQL Server database configured for French language, you may encounter the following error message when manipulating datetime values for embedded SQL statements:
[SQLSTATE = 22007] [Microsoft SQL Server Native Client 10.0] The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Environment
-
PowerBuilder
-
Microsoft SQL Server 2008
Reproducing the Issue
Configure your PowerBuilder connection settings as follows:
SQLCA.DBMS ="SNC SQL Native Client(OLE DB)" SQLCA.ServerName = "MySQLServer2008" SQLCA.LogId = "MyUser" SQLCA.LogPass = "MyPassword" SQLCA.AutoCommit = False SQLCA.DBParm ="Provider='SQLNCLI10',Database='MyDatabase'"
Execute this PowerBuilder script:
datetime ldt string ls select MyDateTime into :ldt from MyTable where MyId = 1; // this select should return a singleton and ldt could equal to 24/10/2013 00:00:00 for instance select MyString into :ls from MyTable where MyDateTime = :ldt;// error message appears here !!!
Cause
By default, PowerBuilder interprets datetime columns with this format: yyyy-mm-dd hh:mm:ss.fff Since SQL Server is set to French, it accepts another format yyyy-mm-dd hh:mm:ss.fff for datetime columns and will treat them as is. As a result, it understands the day as a month and vice-versa. In our previous example, 24 is not a valid month, which will cause the error message
Solution
Configure the DateTimeFormat database parameter in conjunction with the ProviderString database parameter to force a datetime format and a specific language. In our previous example, modify the DBParm property as follows:
SQLCA.DBParm ="Provider='SQLNCLI10',Database='MyDatabase',DateTimeFormat='\''yyyy-dd-mm hh:mm:ss.fff\''',ProviderString='Language=French'"
