MySQL, handling 0000-00-00 date

[vejrous]

Hi,
is there a way how to handle '0000-00-00' date in Zeos?
I can not access is as string (empty '' string is shown).
E convert error is raised when trying to save value.

I understand that it is not proper date, but this allows for storing uncomplete dates 1920-10-00, 0000-05-18, etc.

Thanks for help.

[Fr0sT]

I know very little about MySQL but I've read that `SET sql_mode = '';` tells engine to accept these values

[vejrous]

I know about this SQL command. It is required for MySQL 5.7 and higher.

Code: Select all

SET sql_mode = '';

or

Code: Select all

SET GLOBAL sql_mode = '';

But the problem, I think, is in the conversion. It seems to me it does not know what to do with such dates, so it returns emty string when reading them from DB a produces conversion error when trying to save them.

[marsupilami]

Hello,

currently Zeos doesn't support these dates because Delphi doesn't support these dates in TDateField. If you want to use them you need to convert them to characters for select statements and convert back from characters upon insert and update. TZUpdateSQL is your friend in these cases. If you want to use TZUpdateSQL on MySQL remeber to set CLIENT_FOUND_ROWS=1 to the TZConnection.Properties. See https://sourceforge.net/p/zeoslib/wiki/Things to know about the Zeos 7.2 release/ for more details.

Best regards,

Jan

[vejrous]

If I understand you correctly it means using something like

Code: Select all

SELECT
CAST(DateColumn AS CHAR)
FROM
Table

and than TZUpdate to save data to DB?

This also means write conversion utilities to display date correctly with locale setting.

So am currently thinking it is not better to save date as Int(8), because it is not usual to support this date style.
So it will become for example 20180101 or with unknown date 20180001, 20180100.

[marsupilami]

Hello vejrous,

in principle - yes. That is my suggestion. The main problem is that a date like 00-00-2018 is not supported as a valid date by any control or function or library.

Best regards,

Jan

[Fr0sT]

Yep - I'd say "incomplete" dates are incorrect concept.