It's been my experience that datetime (and related) DB column types vary widely from DB to DB, so I'm not surprised if MySql doesn't like the 'native' formatting coming out of your TD appl. Here's the comment section from some code I once wrote when porting a TD appl. from SQLBase to Oracle:
! Oracle wants format: DD-MMM-YY hh.mm.ss.xxxxxx AM[PM]
! sample Oracle TS : 16-JUN-05 10.29.46.000000 AM[PM]
So, once we understood that
1 - Found out what format Oracle did like;
and
2 - Oracle could interpret a date/time as a correctly-formatted string value, like sOracleDate = '16-JUN-05 10.29.46.000000 AM'
we were in business! In our case, once a query returned the timestamp, we had a class instance convert it to Oracle format, like this:
Set s_ts_converted = oFmt4_Oracle.FmtTS4_Oracle( d_timestamp )
and simply used the SalDateXXX() functions to parse out the d/t components and build the Oracle string. Sounds like that might be a solution for you with MySql.
Datetime error with mysql
Re: Datetime error with mysql
Didier: It is VERY confusing to have duplicate, parallel TD issues on both the gupta NG and here. You (and others) are posting duplicate replies and Jim's last response to you on the gupta NG is the correct one:
Your 'problem' is that MySql has a different format it expects for a DATE value. Since your code example makes it appear that '2008-01-01 01:01:01.9999' is *NOT* the correct format for that DB, you need to look at some d/t values already in your DB and add a little code to reformat your date/time strings to conform to what MySql expects.
It is *not* a bug with TD, nor with ODBC, nor with MySql; in fact, there is no bug anywhere. It is simply the difference in d/t formats for different databases. You can't possibly expect to be able to support a new DB without making at least some modifications to your 800 000 lines of code, right
Your 'problem' is that MySql has a different format it expects for a DATE value. Since your code example makes it appear that '2008-01-01 01:01:01.9999' is *NOT* the correct format for that DB, you need to look at some d/t values already in your DB and add a little code to reformat your date/time strings to conform to what MySql expects.
It is *not* a bug with TD, nor with ODBC, nor with MySql; in fact, there is no bug anywhere. It is simply the difference in d/t formats for different databases. You can't possibly expect to be able to support a new DB without making at least some modifications to your 800 000 lines of code, right

Re: Datetime error with mysql
to Unify: Do you agree to Jeff´s opinion, to not use bind variables for inserting and updating date values in MySql?
I had the same problem with bound date vars. I worked around this by changing my code for MySql databases. But this is - let´s say - not very convenient.
I had the same problem with bound date vars. I worked around this by changing my code for MySql databases. But this is - let´s say - not very convenient.
Re: Datetime error with mysql
Maybe it is a language issue, Lars, but just to be very clear I never did say: "Do you agree to Jeff´s opinion, to not use bind variables for inserting and updating date values in MySql?"
I never said do not use bind variables. Nor did I mean to suggest that.
I *did* say that the string bind variable for a date/time value must be in the *correct* format for MySql (as well as other DB types) to accept the value.
I never said do not use bind variables. Nor did I mean to suggest that.
I *did* say that the string bind variable for a date/time value must be in the *correct* format for MySql (as well as other DB types) to accept the value.
Re: Datetime error with mysql
Sorry Jeff for the missunderstanding and my wrong interpretation!
But, when you say, date values have to be set as strings rather than date format, I would come to the conclusion, that date bind variables have to be formated to string in the right format to use them with MySql. That can not be the right way to work in a database development environment! So there must be a solution for that and no further workaround.
But, when you say, date values have to be set as strings rather than date format, I would come to the conclusion, that date bind variables have to be formated to string in the right format to use them with MySql. That can not be the right way to work in a database development environment! So there must be a solution for that and no further workaround.
Who is online
Users browsing this forum: [Ccbot] and 3 guests