Laravel Version
12.51
PHP Version
8.3
Database Driver & Version
MS SQL SERVER
Description
The fix for
introduces datetime2 as a possible column type that was not used previously. it should only be used if prcision is specified - even 0. However in my code I do not specify precision in the call to dateTime function and float
$table->float('BookingPercentageFee',0)->nullable();
$table->dateTime('FromDate',)->precision() ->nullable()->index('IX_Tbl_Courses_FromDate');
$table->dateTime('ToDate')->nullable();
this is from the SQLServerGrammar.php file in the dateTime / Timestamp function
return ! is_null($column->precision) ? "datetime2($column->precision)" : 'datetime';
In the code above no precision is applied to the dateTime function but the resulting SQL code creates a datetime2 field. Precision should be null for the calls to dateTime() and thus return datetime field type but somewhere along the line gets turned into 0, maybe here.
https://github.com/laravel/framework/blame/4c4d07ac5626b029ca4756ae9213f1d739a4560d/src/Illuminate/Database/Schema/Builder.php#L49-L51
For the float field it 0 used to mean null or false so it woudl work, now
default precision is actually set to 0 not null meaning that I recieve an error when this migration is run.
The codes intention by my reading is that when I call
$table->dateTime('fromdate')->nullable();
It should create a datetime column, not a datetime2 column. This is my desired behaviour.
Additionally it breaks
$table->float('fieldname',0);
which used to work as 0 was evaluated as false for the if statement. This may be desirable, but users should be notified.
Steps To Reproduce
Using 12.51 run a migration with creating a column with a datetime with no precision.
Database must be MS SQL Server
Schema::create('Tbl_Courses', function (Blueprint $table) {
$table->integer('CourseID', true);
$table->integer('CourseReference')->nullable();
$table->string('CourseName', 170)->nullable();
$table->text('Description')->nullable();
$table->tinyInteger('CourseType')->nullable()->index('IX_Tbl_Courses_Type');
$table->dateTime('FromDate')->nullable()->index('IX_Tbl_Courses_FromDate');
$table->dateTime('ToDate')->nullable();
$table->decimal('BreakagesDeposit', 19, 4)->nullable();
$table->decimal('CleaningDeposit', 19, 4)->nullable();
$table->decimal('TotalFee', 19, 4)->nullable();
$table->tinyInteger('CourseCancelled')->nullable();
$table->smallInteger('TotalNumber')->nullable();
$table->smallInteger('BalDueDays')->nullable();
$table->decimal('BudgetedAmount', 19, 4)->nullable();
$table->boolean('Finished')->nullable();
$table->float('BookingPercentageFee', 0, 0)->nullable();
$table->text('Report_Description')->nullable();
$table->float('CourseDuration', 24, 0)->nullable();
$table->tinyInteger('AccomAvailable')->nullable();
$table->integer('info_Demos')->nullable();
$table->integer('info_Practicals')->nullable();
$table->string('RelatedCourses', 300)->nullable();
$table->tinyInteger('CanPublish')->nullable()->default(1)->comment('Whether or not the course should go on website.');
$table->string('Keywords', 400)->nullable();
$table->tinyInteger('WaitingList')->nullable()->default(0);
$table->text('Notes_Checkin')->nullable();
$table->tinyInteger('PreventBooking')->nullable();
$table->tinyInteger('IncludesLunch')->nullable();
$table->tinyInteger('CourseLocked')->nullable();
$table->integer('StaffDiscountProvided')->nullable();
$table->string('RecipeBaseList', 400)->nullable();
$table->string('CoursePart', 10)->nullable();
$table->dateTime('last_accessed_at')->nullable();
});
I tried to reproduce with a small set but was unable to.
Error that i get when running my migration.
SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Line 1: Length or precision specification 0 is invalid. (Connection: sqlsrv_test, Host: BORGZEN\SQLEXPRESS, Port: 1433, Database: CourseManagerTest, SQL: create table "Tbl_Courses" ("CourseID" int not null identity primary key, "CourseReference" int null, "CourseName" nvarchar(170) null, "Description" nvarchar(max) null, "CourseType" tinyint null, "FromDate" datetime2(1) null, "ToDate" datetime2(0) null, "BreakagesDeposit" decimal(19, 4) null, "CleaningDeposit" decimal(19, 4) null, "TotalFee" decimal(19, 4) null, "CourseCancelled" tinyint null, "TotalNumber" smallint null, "BalDueDays" smallint null, "BudgetedAmount" decimal(19, 4) null, "Finished" bit null, "BookingPercentageFee" float(0) null, "Report_Description" nvarchar(max) null, "CourseDuration" float(24) null, "AccomAvailable" tinyint null, "info_Demos" int null, "info_Practicals" int null, "RelatedCourses" nvarchar(300) null, "CanPublish" tinyint null default '1', "Keywords" nvarchar(400) null, "WaitingList" tinyint null default '0', "Notes_Checkin" nvarchar(max) null, "PreventBooking" tinyint null, "IncludesLunch" tinyint null, "CourseLocked" tinyint null, "StaffDiscountProvided" int null, "RecipeBaseList" nvarchar(400) null, "CoursePart" nvarchar(10) null, "last_accessed_at" datetime2(0) null))
Work Around
I have found a workaround which is to use an undocumented function precision which seems to correctly set the precision to null
$table->float('BookingPercentageFee')->precision(null)->nullable();
$table->dateTime('FromDate')->precision(null) ->nullable()->index('IX_Tbl_Courses_FromDate');
$table->dateTime('ToDate')->precision(null)->nullable();
This correctly sets the fields to the previously generated column values.
