1. David Peace (Powersoft)
  2. PowerBuilder
  3. Monday, 19 February 2024 11:26 AM UTC

Problem with PB19 (and I expect with PB22) inserting DateTime data into SQL Server (V2019) using either SQL Native Client or MSOLEDB. If the Login's Default Language is "British English - British" then we get an error:

MS OLE DB Driver for SQl Server The Conversion of a Varchar DataType to a datetime data type resukted in an out-of-range value

The Following code is being used:

// Insert new row into System Audit table
datetime ldt_now
ldt_now = datetime(today(),now())

INSERT INTO DataCache.SystemAudit


Change the default back to "English - us english" then the problem goes away.

The issue is that for Bristish English the DateFormat is dmy and for "us english" it is mdy. If you issue this command after connecting to the database then it fixes the issue:

Connect using SQLCA;
Execute Immediate "SET DATEFORMAT mdy";

The questionis why a bound DateTime in PB is being passed tot he DB as a Varchar and why it doe snot use the DateFormat in the Login environment to determine the format fo the Varchar if it does?

Has anyone else found this or should I raise it as a bug?


Accepted Answer
Markus Eckert Accepted Answer Pending Moderation
  1. Monday, 19 February 2024 13:46 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi David

We've added the following two lines to our connection's dbparm property to force the date/time format PB uses when substituting a parameter to be language-safe.

atr_tr.dbparm += ",DateFormat='{\d \''yyyy-mm-dd\''}'"
atr_tr.dbparm += ",TimeFormat='\''hh:mm:ss.fff\'''"

Would that solve your problem?


  1. David Peace (Powersoft)
  2. Monday, 19 February 2024 14:20 PM UTC
That is only useful if you know the date format in the Login Session, you can select this after you have connected (but that's too late) by using this select:

select date_format

from sys.dm_exec_sessions

where session_id = @@spid;

We have a work around to set the DateFormat back to mdy after we have connected. All I'm suggesting is that PB should be using the select above to know what format the dates should be in.

This will return dmy or mdy etc, we have then used Execute Immediate "SET DATEFORMAT mdy"; to get PB to work correctly with the database, thus overriding the defauly of dmy.
  1. Helpful
  1. Markus Eckert
  2. Monday, 19 February 2024 14:48 PM UTC

The idea behind forcing the ODBC date format is that it's language independent.

It's always going to be interpreted the same, no matter what default language the login has set.

You can try this by running this simple statement under logins using different languages:

SELECT {d '2024-02-11'}

  1. Helpful 2
  1. David Peace (Powersoft)
  2. Tuesday, 20 February 2024 15:26 PM UTC
Surely PB should look atthe date format in the Session and format the Strings accodingly? Or is that an enhancement request?
  1. Helpful
There are no comments made yet.

There are replies in this question but you are not allowed to view the replies from this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.