使用SQL Server 2008的Laravel抛出“将varchar数据类型转换为日期时间数据类型导致超出范围的值”

I am using laravel 4.1 with SQL Server 2008

我正在使用laravel 4.1和SQL Server 2008

I created a model based on Eloquent orm with timestamp:

我创建了一个基于Eloquent orm的模型，带有时间戳：

class Attendance extends \Eloquent {

protected $table = 'Attendance';

public function users(){return $this->belongsToMany('User', 'Users_Attendance', 'user_id', 'attendance_id');}

}

When I try to insert new fields:

当我尝试插入新字段时：

public function postAttendanceUsers() {
    $attendance = new Attendance;
    $attendance->user_id = Auth::user()->id;
    $attendance->save();
}

Display the following error:

显示以下错误：

SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server] Conversion of a varchar data type to a datetime data type resulted in an out-of-range value. (SQL: insert into [Attendance] ([user_id], [updated_at], [created_at]) values (40, 2015-08-27 12:28:42.000, 2015-08-27 12:28:42.000))

SQLSTATE [22007]：[Microsoft] [SQL Server的ODBC驱动程序11] [SQL Server]将varchar数据类型转换为日期时间数据类型会导致超出范围的值。（SQL：插入[考勤]（[user_id]，[updated_at]，[created_at]）值（40,2015-08-27 12：28：42.000,2015-08-27 12：28：42.000））

3 个解决方案

#1

I created a new method with the following format:

我使用以下格式创建了一个新方法：

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

I hope somebody be useful

我希望有人有用

#2

I would suggest that the date values should have single quotes around them...like '20151130'

我建议日期值应该有单引号...就像'20151130'

Or to be more specific:

或者更具体：

insert into [Attendance] ([user_id], [updated_at], [created_at]) values (40, '2015-08-27 12:28:42.000', '2015-08-27 12:28:42.000')

You could test it with SQL directly, using a refined date:

您可以使用精确的日期直接使用SQL测试它：

insert into [Attendance] ([user_id], [updated_at], [created_at]) values (40, '20150827', '20150827')

If that fails, there are serious compatibility issues with the installation and default language (collation) settings in SQL

如果失败，则SQL中的安装和默认语言（排序规则）设置存在严重的兼容性问题

#3

I use Laravel and SQL Server. Try adding this code to your Eloquent base model. Laravel tries to insert milliseconds and SQL Server doesn't like it

我使用Laravel和SQL Server。尝试将此代码添加到您的Eloquent基础模型中。 Laravel尝试插入毫秒，SQL Server不喜欢它

/**
 * 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);
}

#1