Forum > Database
LInux / MySQL date format
OH1KH:
Hi!
Just wondering have anyone seen problem(s) with dates fetch from MySQL database?
--- Code: Text [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---+----------------+---------------+------+-----+---------+----------------+| 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:
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:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
For testing: have you tried to cast it to date in your SQL?
Just to see what it returns.
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
--- Quote from: Zvoni on November 10, 2022, 09:33:56 am ---For testing: have you tried to cast it to date in your SQL?
Just to see what it returns.
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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)
--- End quote ---
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
Navigation
[0] Message Index
[#] Next page