CODEWITHSUNDEEP
sql-serverlaravel
Amarnasan
Amarnasan

Reputation: 15579

How to force migrations to create datetime2 fields instead of datetime

I'm developing a laravel application accessing a sqlserver database, and I need my date fields to be datetime2. Problem is, if I tell the migration engine to create a datetime field like this:

$table->dateTime('myDateTimeColumn');

if will create a datetime field, not a datetime2 field.

How can I force a datetime2 column without having to change them all AFTER the migration (which sounds very sloppy)?

Upvotes: 4

Views: 2237

Answers (3)

Shafiq al-Shaar
Shafiq al-Shaar

Reputation: 1323

Force a datetime2 by adding a precision int to the method creating the datetime column.

$table->datetime(4);
$table->timestamp(4);
$table->timestamps(4);

Using datetime(), timestamp(), or timestamps() without specifying an integer will make a DATETIME column type on SQL Server and now saving any dates using Laravel will break the code because a DATETIME column type allows only for 3 digits for the microseconds part. Laravel sometimes places 4 digits for the microseconds and thus needing DATETIME2.

I discovered the solution when I was reading Laravel's source file:

Illuminate\Database\Schema\Grammars\SqlServerGrammer.php

It contains this function:

    /**
     * Create the column definition for a date-time type.
     *
     * @param  \Illuminate\Support\Fluent  $column
     * @return string
     */
    protected function typeDateTime(Fluent $column)
    {
        return $column->precision ? "datetime2($column->precision)" : 'datetime';
    }

I made a unit test case on GitHub too. https://gist.github.com/spacemudd/abfa7ef66d096f3f20796bf373df365b

That test fails when you don't specify a precision on migration methods creating datetime columns.

Upvotes: 5

FULL STACK DEV
FULL STACK DEV

Reputation: 15971

you can do the same $table->addColumn('type','name')

$table->addColumn('datetime','myDateTimeColumn');

I hope this helps.

Upvotes: -1

Stefano Groenland
Stefano Groenland

Reputation: 561

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

change the function typeDateTime() so it returns the 'datetime2' instead of datetime.

And for the typeTimestamp() you could do the same thing!

Upvotes: 0

Related Questions