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
(AuditType
,UserID
,ActionDate
,ActionPC
,ActionID
,Description
,TableName)
VALUES
(:as_audittype
,:gs_userid
,:ldt_now
,:gs_machinename
,:as_ActionID
,:as_Description
,:as_TableName);
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?
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.
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'}
https://learn.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver16#supported-string-literal-formats-for-date