Query to write Date/Time not working

Hi,

I've got a query that uses native access to MySQL. One of the fields is defined as a datetime field in the database and of course as a Date in the Analysis.

The problem is how to supply the value to the parameter of the query to execute. I've tried

QRY_Queue_Put.Param_Last_Run_Time =DateSys() + TimeSys() hExecuteQuery(QRY_Queue_Put)
which returns an error:

Incorrect datetime value: '20090203105444860' for column 'Last_Run_Time' at row 1

How do you supply a date time value to a parameter of a query that does an update.

Thanks
Glenn Rathke

Hi Glenn,

Did you subtype the date field in the WD analysis to the correct DateTime type?

Cheers,

Hi Glenn,

you can use datetostring(date,"YYYY-MM-DD") + timetostring(time,"HH:MMS:CC") to format this for MySQL ( not tested only idea ).

Christoph

Hi Glenn,

Look at [forum.mysnip.de]

Regards,
Piet

Y don't know how it works with MYSQL but with SQLSERVER with native access the way to handle date fields in the analysis is diferent for HQUERYDEFAULT or HQUERYWITHOUTCORRECTION parameter.

If you use HQUERYDEFAULT you only put QRY_Queue_Put.Param_Last_Run_Time = datesys() and the native access convert to the rigth format.

If oyu use HQUERYWITHOUTCORRECTION you have to respect the DB format, for SQLSERVER you must use CONVERT / CAST to change the format for MYSQL i'm not sure but i think that it's the same.