LInux / MySQL date format lazarus / 63-Database ---------------------------------------------------------------------- OH1KH · 2022-11-06 10:29 Hi! Just wondering have anyone seen problem(s) with dates fetch from MySQL database? Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i +---------- ------+---------------+------+-----+---------+----------------+| Field          | Type          | Null | Key | Default | Extra          |+--- -------------+---------------+------+-----+---------+---------------- +| id_cqrlog_main | int(11)       | NO   | PRI | NULL    | auto_increment || qsodate        | date          | NO   | MUL | NULL  |                |   I have one program that randomly loses dates saved in database. It just gives a splash "2022-11-04 is not valid date format Abort/OK" What is sad is that I have not found any way to reproduce this so that it could be debugged properly. It just happens, then may be several usage times without a problem and then just randomly pops up again. Normally restarting program helps right away, do dates are in SQL table OK. Making addition (any number)  to date in SQL sentence brings it up. See this issue: https://github.com/ok2cqr/cqrlog/issues/540 I just wanted to ask has anyone had this kind of "valid date format error" with some other FPC program using MySQL? ---------------------------------------------------------------------- Zvoni · 2022-11-06 12:05 Is qsodate really a date-type, or is it some integer-formatted date? Looking at your github link i think it might be the second (close to the end of github link where you query qsodate+0) ---------------------------------------------------------------------- OH1KH · 2022-11-10 05:45 Hi! As seen from 1st message the "qsodate" is SQL type "date"  (YYYY-MM- DD) When things work normally it looks like YYYY-MM-DD in programs view. When I look it with command console's mysql: Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MariaDB [cqrlog003]> select qsodate from cqrlog_main where id_cqrlog_main=13296;+------------+| qsodate    |+------------+| 2022-11-04 |+------------+1 row in set (0.001 sec) MariaDB [cqrlog003]> select qsodate+0 from cqrlog_main where id_cqrlog_main=13296; +-----------+| qsodate+0 |+-----------+|  20221104 |+-----------+1 row in set (0.002 sec) MariaDB [cqrlog003]> As seen from added issue link when date is added with zero the output format changes. It does it also with command console's mysql. As the this program has it's internal SQL console (that uses FPC/SQL unit) that displays query results "as is" it makes me wonder why it does not pass YYYY-MM-DD, but passes YYYYMMDD (when zero added to date). That really looks like the SQL unit gets randomly to faulty state, but I can not find a way to reproduce this regularly for debugging to be sure it is in SQL unit. ---------------------------------------------------------------------- Zvoni · 2022-11-10 08:33 For testing: have you tried to cast it to date in your SQL? Just to see what it returns. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT CAST(qsodate+0 AS DATE) AS testDate FROM cqrlog_main WHERE id_cqrlog_main=13296;btw: Don't forget, that a Date-Type is basically an INTEGER (or Double if DateTime) ---------------------------------------------------------------------- dsiders · 2022-11-10 16:36 Quote from: Zvoni on November 10, 2022, 09:33:56 amFor testing: have you tried to cast it to date in your SQL? Just to see what it returns. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT CAST(qsodate+0 AS DATE) AS testDate FROM cqrlog_main WHERE id_cqrlog_main=13296;btw: Don't forget, that a Date-Type is basically an INTEGER (or Double if DateTime) Maybe I'm missing the point here... Why not use the built-in date functions? Like DATE_ADD(). https://dev.mysql.com/doc/refman/8.0/en/date-and-time- functions.html#function_date-add ---------------------------------------------------------------------- Zvoni · 2022-11-10 16:55 Quote from: dsiders on November 10, 2022, 05:36:01 pmQuote from: Zvoni on November 10, 2022, 09:33:56 amFor testing: have you tried to cast it to date in your SQL? Just to see what it returns. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT CAST(qsodate+0 AS DATE) AS testDate FROM cqrlog_main WHERE id_cqrlog_main=13296;btw: Don't forget, that a Date-Type is basically an INTEGER (or Double if DateTime) Maybe I'm missing the point here... Why not use the built-in date functions? Like DATE_ADD(). https://dev.mysql.com/doc/refman/8.0/en/date-and-time- functions.html#function_date-add OP‘s problem is that sometimes his program throws an error that „YYYY- MM-DD“ is an invalid dateformat ---------------------------------------------------------------------- OH1KH · 2022-11-11 08:16 HI ! Thanks for all answers so far! I do not need "qsodate+0", I just found out that when qsodate returns as completely empty string (see the link to GitHub issue, in 1st message) then making addition to to qsodate at SQL side makes it visible at program's side. I.E. query should produce "qsodate" as YYYY-DD-MM in program's grid or program's SQL console. How ever sometimes, randomly, it happens that "qsodate" is completely empty. This is random and rare, so debugging is difficult. I those cases testing with program's SQL console is the only way to pinpoint something because restarting program usually removes the bug. Then I found out that adding something to qsodate at SQL side passes it to program display because the format of date changes to one without "-" chars in between. Without adding should  YYYY-MM-DD appear, but the output is empty. Because programs SQL console is "directly connected" to query result (means that program does not process result before displaying in grid) and command line mysql gives right result, I have strong feeling that FPC's SQL unit has some kind of underlying bug. That bug may be depended on OS locales. My locales include LC_TIME="fi_FI.UTF-8" and the way SQL saves and program displays date (YYYY-MM-DD) is not the one by locales that is DD.MM.YYYY  Perhaps that makes it sometimes fail. But as log as I can not catch the bug repeatedly I can not be sure and make a bug report. I just wanted to know has anyone else ever noticed same kind of results. In that case I would, maybe, put more efforts for tracking it. ---------------------------------------------------------------------- Zvoni · 2022-11-11 08:28 Quote from: OH1KH on November 11, 2022, 09:16:27 amI just wanted to know has anyone else ever noticed same kind of results. In that case I would, maybe, put more efforts for tracking it. In short: No. OTOH: Since we don't know any real SQL-Statements of yours, it's still guesswork what it might be. We don't know if there are any calculations, scalar-functions or window-functions involved which might influence your results