MySQL and Date

[Himky]

Hi,

I'm trying to use a MySQL Database (ODBC 5.1) with TD 5.1 but I have a big problem.

I started a sample with 1 request to MySQL : a simple UPDATE.

I have created a Datafield (dnow) that contains the actual date (SalDateCurrent())
Then with that request : UPDATE MYTABLE SET MYDATE = :dnow WHERE MYID=3 I always get 0000-00-00 in the database table (check done with phpmyadmin)

I hope I've give enough information. Nota : Other bind variable like String work perfectly !

Please someone could help me ?

Thanks
Himky

[jmccall]

A long time ago I discovered that an Oracle database was happier if I inserted & updated dates using strings, as follows:

INSERT INTO testDateIns ( dateCol ) VALUES ( TO_DATE( :sVar , 'YYYYMMDD HH24:MI:SS' ) )
INSERT INTO testDateIns ( dateCol ) VALUES ( TO_DATE( :sVar , 'YYYYMMDD' ) )

Maybe MySQL has a similar quirk?

Two notes to add to this point:

1. SQL Server did not like working this way, that is, the following did not work ...
INSERT INTO testDateIns ( dateCol ) VALUES ( CONVERT( DATETIME, :sVar ) )
INSERT INTO testDateIns ( dateCol ) VALUES ( CONVERT( DATETIME, CONVERT( VARCHAR( 21 ) , :sVar, 112 ) )
... so my code, which has to work on both DBMS's, has been coded to know the difference.

2. This was determined a long time ago, using CTD2000 and SQL Server v7 / Oracle 8i. Things may have changed in the DBMS's or the routers since then. However it still might be worth trying out in MySQL.

Usually I would say "hope this helps" but I actually hope it doesn't because it would be better if these anomolies no longer exist with TD5.1 routers!

Regards,
James McCall