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

  1. 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'"

  1.  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 defaut, 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

 

Resolution

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'"

3
0