Laravel Mssql Dateformat Laravel Package

Getting Started

This package (laravel-mssql-dateformat) bridges Laravel's Eloquent with Microsoft SQL Server's date formatting quirks. First step: Install via Composer:

composer require tobya/laravel-mssql-dateformat

Publish the config (if needed) with:

php artisan vendor:publish --provider="TobyA\MssqlDateFormat\MssqlDateFormatServiceProvider"

First use case: Automatically format dates in queries to avoid SQL Server's DATE/DATETIME precision errors. No manual casting required—just use Eloquent as usual.

Implementation Patterns

Core Workflow

1. Automatic Date Handling: The package hooks into Eloquent's query builder to convert Laravel's Carbon instances to SQL Server-compatible formats (e.g., CONVERT(DATETIME, ...)). No explicit configuration needed for basic use.
2. Custom Formatting: Override default behavior by defining rules in config/mssql-dateformat.php:

   'formats' => [
       'datetime' => 'Y-m-d H:i:s', // Custom format for DATETIME columns
   ],
   

3. Manual Overrides: Use the formatForMssql() helper or MssqlDateFormat::format() method for one-off cases:

   $formatted = formatForMssql($carbonInstance, 'datetime');
   

Integration Tips

• Migrations: Use the package's MssqlDateTime column type for new tables:

  Schema::create('events', function (Blueprint $table) {
      $table->mssqlDateTime('scheduled_at');
  });
  

• API Responses: Pair with Fractal or Transformers to ensure consistent date serialization across layers.
• Testing: Mock the MssqlDateFormat facade in unit tests to isolate date logic:

  $this->partialMock(MssqlDateFormat::class, function ($mock) {
      $mock->shouldReceive('format')->andReturn('2023-01-01 00:00:00');
  });
  

Gotchas and Tips

Pitfalls

1. Laravel 11+ Caching: If using Laravel 12, clear cached views/config after updating:

   php artisan config:clear
   php artisan view:clear
   

2. Timezone Mismatches: SQL Server defaults to UTC. Ensure your Carbon instances are timezone-aware or explicitly set:

   $carbon->setTimezone('UTC');
   

3. Reserved Keywords: Column names like order or group may conflict with SQL Server syntax. Use backticks or alias them in queries.

Debugging

• Query Inspection: Enable Laravel's query logging to verify date formatting:

  DB::enableQueryLog();
  User::where('created_at', today())->get();
  dd(DB::getQueryLog());
  

• Raw Queries: The package doesn’t modify raw queries. Use DB::raw() with caution—manually format dates if needed.

Extension Points

1. Custom Drivers: Extend the MssqlDateFormat class to support other databases (e.g., PostgreSQL):

   class CustomDateFormat extends MssqlDateFormat {
       public function format($value, $type = 'datetime') {
           // PostgreSQL logic here
       }
   }
   

2. Event Hooks: Listen for eloquent.query events to inject custom formatting logic:

   Event::listen('eloquent.query', function ($query) {
       if ($query->getConnection()->getDriverName() === 'sqlsrv') {
           // Modify query bindings
       }
   });
   

3. Testing Helpers: Create a trait for reusable test setups:

   trait MssqlDateFormatTest {
       protected function assertMssqlDateFormat($expected, $actual) {
           $this->assertEquals($expected, $actual, '', 0.0001);
       }
   }
   

Laravel 12-Specific Notes

• No Breaking Changes: The update to Laravel 12 (v0.6) is a drop-in upgrade. Existing configurations and usage remain identical.
• Bootstrap Changes: If using Laravel's new bootstrap order, ensure the service provider is registered in config/app.php under providers (it should be auto-discovered, but verify for custom setups).