Timestamp/DateTime issue with 5.1 & SqlServer

Hi!

I had a similar issue w/Lumen; what I did was to override the getDateFormat() method in a BaseModel class which every other model will extend like this

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class BaseModel extends Model
{
    /**
     * @return string date format for mssql
     */
    protected function getDateFormat()
    {
        return 'Y-m-d H:i:s.u0';
    }
}

Thanks @toby

Also found the save() was solved adding to a method using the BaseModel approach I found from https://github.com/laravel/framework/pull/4181

Ended up adding these 2 methods.

/**
 * Get the format for database stored dates.
 *
 * @return string
 */
public function getDateFormat()
{
    return 'Y-m-d H:i:s.u';
}

/**
 * Convert a DateTime to a storable string.
 * SQL Server will not accept 6 digit second fragment (PHP default: see getDateFormat Y-m-d H:i:s.u)
 * trim three digits off the value returned from the parent.
 *
 * @param  \DateTime|int  $value
 * @return string
 */
public function fromDateTime($value)
{
    return substr(parent::fromDateTime($value), 0, -3);
}

Laravel still throws an conversion error after trying googled things and this here. I ended up modifying the File: \vendor\laravel\framework\src\Illuminate\Database\Schema\Grammars\SqlServerGrammar.php like this:

    /**
     * Get the format for database stored dates.
     *
     * @return string
     */
    public function getDateFormat()
    {
        return 'd.m.Y H:i:s';
    }

That fixed it for me.

I know this question has a "best answer" but this solution worked for me only when no Pivot-Table was used. I tried to use Mutators, but this wasn't possible on Pivot-Tables.

If you struggle with the datetime in MSSQL you should check the MSSQL User you use to connect to the DB

SELECT dateformat FROM sys.syslanguages where name = @@LANGUAGE;

This returned "dmy" and I needed "myd" what seems to be the default setting on MSSQL.

So try to

SET LANGUAGE 'us_english'

I hope this will help someone!

See also https://database.guide/how-to-change-the-current-date-format-in-sql-server-t-sql/

This should affect any Laravel version up to 5.5. To anyone having issues with SQL Server, this should fix any issues while working with dateTime() and timestamp() columns:

/**
 * Get the format for database stored dates.
 *
 * @return string
 */
public function getDateFormat()
{
    return 'Y-m-d H:i:s.u';
}

/**
 * Convert a DateTime to a storable string.
 *
 * @param  \DateTime|int  $value
 * @return string
 */
public function fromDateTime($value)
{
    return $value->format('Y-m-d H:i:s.v');
}