Datetime error with mysql

General discussion forum about all databases other than SqlBase.
Jeff Luther

Datetime error with mysql

Post by Jeff Luther » 01 May 2008, 19:31

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.

Jeff Luther

Re: Datetime error with mysql

Post by Jeff Luther » 02 May 2008, 15:44

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 :?:

lars

Re: Datetime error with mysql

Post by lars » 03 May 2008, 12:07

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.

Jeff Luther

Re: Datetime error with mysql

Post by Jeff Luther » 03 May 2008, 20:20

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.

lars

Re: Datetime error with mysql

Post by lars » 05 May 2008, 05:23

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.

lars

Re: Datetime error with mysql

Post by lars » 05 Jun 2008, 06:30

this persists in SP3.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 3 guests