Dummy mysql date ie. '0000-00-00' is presented as -0001-11-30 in Cycle in a View

[rossaddison]

What steps will reproduce the problem? Presenting a DateTimeImmutable as a string in a view using:

eg. ($client->getClient_birthdate())->format($datehelper->style())

What is the expected result? A legitimate date.

What do you get instead? -0001-11-30

Additional info

Q	A
mySql version	8.0.27
PHP version	8.1.9
Operating system	Windows 10

[xepozz]

What does $datehelper->style() provide?

[rossaddison]

The $datehelper->style() provides the format 'Y-m-d'. This is the format that I have chosen for my dates to be presented as. I could have chosen one of 13 formats.

To elaborate:

The user of the software can go into settings and select a date format of their choice whether it be d.m.Y or Y-m-d out of a possible 13. This dropdown is available under Settings...View within the software itself.

[rossaddison]

I have managed to resolve the problem with the following code. Perhaps you can suggest something better.

I am using the below code to present this date as a dummy date in the view or even as an empty string.
eg.
(($client->getClient_birthdate())->format($datehelper->style())) === '-0001-11-30' ? '0000-00-00'
:
(($client->getClient_birthdate())->format($datehelper->style()))

[rossaddison]

I dont know why Cycle returns the '-0001-11-30'. Is this a substitute date in cycle as well? There is nothing in the documentation and nothing in their issues as well.

[xepozz]

What time inside getClient_birthdate()? Could you dump it with var_dump() and show the result?

[rossaddison]

Ok just var_dumped it and I get the following:

object(DateTimeImmutable)#4227 (3) { ["date"]=> string(27) "-0001-11-30 00:00:00.000000" ["timezone_type"]=> int(3) ["timezone"]=> string(3) "UTC" }

[rossaddison]

I am going to leave this topic open. I am going to present the date as '0000-00-00' for the time being.

[xepozz]

What's value of that cell in db?
I think you store -0001-11-30 00:00:00.000000 in the db and that's why PHP shows you that string.

[rossaddison]

The value of the cell in db is '0000-00-00'. I have read the following in mySql documentation:

[5.1.11 Server SQL Modes](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html)
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. NO_ZERO_DATE The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. If this mode is not enabled, ... The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system ...
[B.3.4.2 Problems Using DATE Columns](https://dev.mysql.com/doc/refman/8.0/en/using-date.html)
https://dev.mysql.com/doc/refman/8.0/en/using-date.html
The special “zero” date '0000-00-00' can be stored and retrieved as '0000-00-00'. When a '0000-00-00' date is used through Connector/ODBC, it is automatically converted to NULL because ODBC cannot handle that kind of date. MySQL permits you to ...You should use this format in UPDATE expressions and in the WHERE clause of SELECT ...

[rossaddison]

I get this if I go into the wampserver menu.

[rossaddison]

I am currently using in my.ini:

; Set the SQL mode. See Documentation:
; https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
sql_mode=""
;sql_mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE"

So I am in sql.mode=""

[rossaddison]

This is an interesting article: Incorrect date value

[rossaddison]

So I am NOT running in strict mode and therefore the mySql is allowing these '0000-00-00' dates.

I think the solution to avoid this issue completely is simply to set the default date in the php, whether in the controller or in the views for unfilled dates to:

(new \DateTimeImmutable('now'))->format(..user.'s date...choice). ....... which gets converted to mySql's YYYY-mm-dd
format automatically.

This will ensure that the current date is at least in the mySql field and in any datepicker and this will avoid the '0000-00-00' issue completely.

So forms have a default date of the current date.

[rossaddison]

The above solution works but I will leave this open for discussion.

[xepozz]

@roxblnfk do you know how to solve it in Cycle?

[rossaddison]

If you can point me in the right direction I would appreciate it. I will raise an issue in Cycle.

[rossaddison]

I have raised an issue here

[samdark]

Closing since it's not issue of the Yii framework itself.