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

General discussion forum about all databases other than SqlBase.
klaushaidinger
Austria
Posts: 118
Joined: 13 Dec 2017, 06:08
Location: Austria

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

Post by klaushaidinger » 17 Oct 2020, 07:43

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
Germany
Posts: 17
Joined: 18 Mar 2020, 10:32
Location: Germany

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

Post by ODIM » 17 Oct 2020, 09:40

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
Regards,
ODIM

klaushaidinger
Austria
Posts: 118
Joined: 13 Dec 2017, 06:08
Location: Austria

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

Post by klaushaidinger » 17 Oct 2020, 09:50

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
Germany
Posts: 17
Joined: 18 Mar 2020, 10:32
Location: Germany

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

Post by ODIM » 17 Oct 2020, 10:01

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

klaushaidinger
Austria
Posts: 118
Joined: 13 Dec 2017, 06:08
Location: Austria

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

Post by klaushaidinger » 19 Oct 2020, 16:20

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
Mauritius
Posts: 25
Joined: 21 Jun 2017, 02:07
Location: Mauritius

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

Post by visionpro » 20 Oct 2020, 15:19

Use OLEDB instead of ODBC and it will work fine

Rgds

s.

andeni
Australia
Posts: 6
Joined: 21 Nov 2022, 09:27
Location: Australia

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

Post by andeni » 21 Nov 2022, 13:25

Hello , have you resolve this problem?

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 2 guests