How to force migrations to create datetime2 fields instead of datetime

Question

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)?

Answer 1

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.