I am running XAMPP on a local machine, with PHP 7.1, MS SQL Server Express and obviously Ci4 MVC. I'm based in the UK and the SQL Server is setup to British English (date format d-m-y).
Been trying to debug this issue for a while now, when I try to use either softDelete or Timestamps, I get a date format error (i.e. d-m-y numbers are in the wrong positions). I had put a fix in the Entity to use callback to reformat the date. That worked fine on the update date, but not on the delete date (that's because the date is not in the $data array for the callback, only the PK of the record to delete.
I eventually checked the BaseModel.php file to see if I could fine the cause, and noticed the two date format functions are using HARDCODED date formats when returning the dates for the update_at and delete_at fields. And the format is Y-M-D ! Fine if I'm using MariaDB but not if I use SQL Server.
PHP Code:
/**
* A utility function to allow child models to use the type of
* date/time format that they prefer. This is primarily used for
* setting created_at, updated_at and deleted_at values, but can be
* used by inheriting classes.
*
* The available time formats are:
* - 'int' - Stores the date as an integer timestamp
* - 'datetime' - Stores the data in the SQL datetime format
* - 'date' - Stores the date (only) in the SQL date format.
*
* @param integer $value value
*
* @return integer|string
*
* @throws ModelException
*/
protected function intToDate(int $value)
{
switch ($this->dateFormat)
{
case 'int':
return $value;
case 'datetime':
return date('Y-m-d H:i:s', $value);
case 'date':
return date('Y-m-d', $value);
default:
throw ModelException::forNoDateFormat(static::class);
}
}
/**
* Converts Time value to string using $this->dateFormat
*
* The available time formats are:
* - 'int' - Stores the date as an integer timestamp
* - 'datetime' - Stores the data in the SQL datetime format
* - 'date' - Stores the date (only) in the SQL date format.
*
* @param Time $value value
*
* @return string|integer
*/
protected function timeToDate(Time $value)
{
switch ($this->dateFormat)
{
case 'datetime':
return $value->format('Y-m-d H:i:s');
case 'date':
return $value->format('Y-m-d');
case 'int':
return $value->getTimestamp();
default:
return (string) $value;
}
}
I'm not sure if anyone else has suffered this, I couldn't fine any articles that referenced this issue, mainly because most people seem to be using MariaDB so it wouldn't be an issue.
I change the format to d-m-y and the softDeletes started working without issue. I don't like changing the system code and would rather not. It's a shame this formatting can't be set in the App.php config file or in the constants so we have more control over it. I quickly checked and there are 40 places in the system code where the date format is hardcoded to Y-M-D. Happy to supply this list if its useful.
Anyway, I hope this helps.