dongqian3198
dongqian3198
2013-12-17 16:49

使用Laravel的pdo_sqlsrv和日期格式

已采纳

I wanted to try Laravel for a while, so I spent the afternoon setting it up.

On Windows I installed: WAMP 32-bit with PHP 5.4, SQL Server 3.0 drivers from Microsoft (that's why I use WAMP 32 bit) and Composer.

Then I created a project, download Laravel and generators. I created a scaffold to see the kind of code it generates and test it.

My first "create" action failed because of a nvarchar to datetime conversion error. I pasted the query in SQL Server Management Studio (SSMS) and fire it with same results.

The mistake comes from the date format. Laravel put it this way: "2013-12-17 16:55:124". But before the request, it sends some set to SQL Server, and one is very interesting:

set dateformat dmy

To me set dateformat dmy means it expects a date like this: "17-12-2013 16:55:124"

In SSMS changing the set dateformat to:

set dateformat ymd

or changing the date format in the request makes it works.

I don't think it is a bug. I just think something is misconfigured. I just don't know what.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doulv1760 doulv1760 8年前

    I finally found a way around which seems quite clean. The trick is to force Laravel to use datetime2 instead of datetime with SQL Server.

    In order to do this you need to do those changes:

    In Illuminate/Database/Schema/Grammars/SqlServerGrammar.php

    protected function typeDateTime(Fluent $column)
    {
        return 'datetime'; // Change to datetime2
    }
    

    And

    protected function typeTimestamp(Fluent $column)
    {
        return 'datetime'; // Change to datetime2
    }
    

    Then in Illuminate/Database/Query/Grammars/SqlServerGrammar.php:

    public function getDateFormat()
    {
        return 'Y-m-d H:i:s.000'; // change to 'Y-m-d H:i:s.0000000'
    }
    

    I hope it helps someone...

    点赞 评论 复制链接分享
  • dongliushui2001 dongliushui2001 4年前

    I fixed it using DB::raw wit SQL's server specific syntax for datetime.

    public function datetime() {
    
        $addDate = DB::insert("insert into TABLE (date) values (convert(date,'$date',127))");
    
    }
    

    I don't know exactly why it happens, but the same function with classic Laravel DB syntax return SQLSTATE[22007].

    public function datetime() {
    
        $addDate = DB::table('TABLE')
        ->insert([
            'date'=> $date]);
    
       // return SQLSTATE[22007]
    }
    
    点赞 评论 复制链接分享