TD 6.3. ODBC driver 17 SqlServer, Date/Time bind variables

[klaushaidinger]

Hi Everyone,

I'm trying to update using a datetime - bind-variable and an ODBC connection (ODBC Driver 17 for SQL Server) - but it doesn't work.

DIAG [22005] [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for conversion specification (0)

With an older version (10.xxx) of the ODBC driver it is still ok. Does anybody have an idea what this might be due to?
Replacing the bind-variable with SalFmtFormatDateTime is not an option for me, because the complete source code would have to be changed.
Thanks for your help.

[ODIM]

i had a similar problem using MySql 5.1

Selects from Tables with Date or Datetime Columns into Bind Variables of Type Date/Time in Gupta works,
but Updates and Inserts doesn´t.
MySql usses an unchangeable Dateformate "yyyy-mm-dd"

My Solution:
for INSERT and UPDATE Statements i use as bindvariable a String Variable like this:

dDate can be a Datafield oder Variable of Type Date/Time.

Code: Select all

Variables :
String: sDate

if Not SalIsNull( dDate )
   Set sDate = SalFmtFormatDateTime( dDate, 'yyyy-MM-dd' )

Next Insert or Update your Table Date Column with the String Variable as Bind
or check that the format of your Date/Time Binding matches your Database Colum Format

[klaushaidinger]

Thanks for your answer!
In MySql I had the same problem with one project. But here I assumed from the beginning that it only works as described by you.
With the SQL Server I already assumed that there is no problem, just because I updated the ODBC driver.
As far as I can see, everything else works, except the date/time problem.

[ODIM]

i would recommend that you test my solution for Sql Server, maby it works there too

[klaushaidinger]

Hi!
Odim, your solution also works with SqlServer - thanks for your suggestion.
But replacing the complete source code with SalFmtFormatDateTime is, as I said, much too complex and not an option for me.
Today I also tried the datetime2 datatype of SqlServer. Unfortunately also no success :-/

[visionpro]

Use OLEDB instead of ODBC and it will work fine

Rgds

s.

[andeni]

Hello , have you resolve this problem?