Why do my ‘date’-type columns have a colon after the seconds?

@Snapey Nope, none – no accessors or mutators or anything at all. And as I say, this happens even if I’m not in a model context at all, but just do a straight-up DB::table()… select. No migrations either – the entire database is pre-populated with production data and not created, seeded or populated by Laravel in any way.

@MohamedTammam As far as I can tell, casting model properties only has an effect when you access the property, just like adding them to the $dates array doesn’t create Carbon date objects until you access the property.

In this case, it’s the string used as the basis for casting or Carbonating that’s the problem, so I don’t think this would solve anything.

@kokoshneta Why don't you cast it and then display it in the format you want?

@MohamedTammam Casting to date or datetime also creates a Carbon date object, which is precisely what fails with an InvalidFormatException thrown. The whole point is that casting doesn’t work.

You can easily try it yourself – paste the following code into a test file or tinker and see the result:

$date = new Carbon("Feb  9 2022 12:00:00:AM");

@Sinnbeck Yup, that works fine. The database itself is on a production server and is used in the existing (non-framework) site where it’s worked perfectly fine for nearly two decades. I’ve only come across this when using Laravel functionality.

I found another GitHub issue that describes the same thing, but for some reason that was closed by the asker “to investigate” and then never revisited.

@kokoshneta I will se if I an set up a sqlsrv server in docker to test it out myself.

What is the version of laravel, php and sql server?

@Sinnbeck Thanks! Although at least one other user (in the other GitHub issue I found) said that they don’t have the issue, so it’s apparently not a universal SQL Server issue.

@kokoshneta I work with a sql server in laravel daily but it does not use datetime. But if I know your exact versions, perhaps I can recreate it and debug it with xdebug

@Sinnbeck Note that datetime works fine – it’s only date specifically that’s broken.

My versions are:

• Laravel Version: 9.3.1 (also present in 8.X before I upgraded)
• PHP Version: 8.1.3 running on macOS 10.15.7 and 12.2.1
• Database Driver & Version: SQL Server 2019 (15.00.2080) on Windows Server 2019 Standard (1809, build 17763.2686)

I don’t think that makes much difference, though, ’cause the other guy who had the issue three years ago had this setup:

• Laravel Version: 5.8.16
• PHP Version: 7.3.5
• Database Driver & Version: MSSQL / Microsoft SQL Server 2016 - 13.0.5292.0

So for those whom it affects, it’s been around for several versions of both Laravel, PHP and MSSQL.

@kokoshneta ok I will give it a shot in docker next time I'm at my computer

Just tried it out with this schema

Schema::create('test', function (Blueprint $table) {
            $table->id();
            $table->date('date');
            $table->timestamps();
        });

I then called this

Route::get('/', function () {
    \DB::table('test')->insert(['date' => now()]);
    return \DB::table('test')->get();
});

which gave me

[
    {
        "id": "1",
        "date": "2022-04-04",
        "created_at": null,
        "updated_at": null
    }
]

I sense I am missing something?

@Sinnbeck Well, the only difference in the table schema is that my table wasn’t created using migrations (it’s an existing production table) – what you’re getting back is precisely what I had expected to get back.

So the problem then becomes figuring out what triggers this bizarre bug.

I wonder if it might make a difference whether SQL Server is running in a container or on an actual Windows Server machine. Yours was containerised, mine is on Windows Server, and the other guy who had the issue didn’t mention either way.

Just out of curiosity, what do you get if you make a datetimeoffset(7) column? Those get transformed to US style for me too, though at least in a non-breaking way, but since your date column doesn’t get transformed at all, I wonder if it’s the same for datetimeoffset columns as well.

@kokoshneta Let me see if I can figure out how to make that. I have never had to add columns in mssql, as I only work with an external system with an already set up database, which I cannot change :)

My guess is

$table->dateTimeTz('date_tz', $precision = 7);

testing

Route::get('/tinker', function () {
    \DB::table('test')->insert([
        'date' => now(),
        'date_tz' => now(),
    ]);
    return \DB::table('test')->get();
});
//returns
[
    {
        "id": "1",
        "date": "2022-04-04",
        "date_tz": "2022-04-04 08:05:36.7530000 +00:00"
    }
]

This is how it looks in dbeaver: https://i.imgur.com/Y3yOKpu.png

@Sinnbeck Yup, that looks like it’s supposed to look as well. So it’s both column types being transformed, and one broken.

Incidentally, I was wrong above – the table I was working with yesterday that made the issue resurface is a migrated table, not an existing one, so migrated vs non-migrated isn’t the issue.

@kokoshneta And the migration is just $table->date('foobar'); ?

@Sinnbeck Yup, just that.

Well, the GitHub issue wasn’t much help – summarily closed as ‘invalid’ with no further reason, referring to past reports about the same issue, though there don’t seem to be any. :-/

@kokoshneta Would i be possible to connect a completely new install of laravel with the database to see if it does the same? Or take the code that "fails" and connect to a new mssql server (docker or similar) ?

@Sinnbeck That’s the next thing I’m going to try. First I’ll try with a plain PDO connection from within the Laravel project (the plain PDO connection I’ve used so far has been on the production server, completely decoupled from any framework), then I’ll try with a fresh Laravel install, then with a containerised MSSQL install if I can figure that out.

@kokoshneta let me know if you need help with that. I set up laravel 9 with mssql in docker using Lando, in a matter of minutes. I assume you are on Mac or Linux

In case you want to give it a shot with lando, here is my config

name: laracasts
recipe: laravel
config:
  webroot: ./public
  php: "8.1"
services:
  mssql:
    type: mssql
    portforward: 1434
  appserver:
    environment:
      ACCEPT_EULA: "Y"
    build_as_root:
      - apt-get update -y
      - apt-get install apt-transport-https -y
      - curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
      - curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
      - apt-get update -y
      - apt-get install msodbcsql17 -y
      - apt-get install unixodbc-dev -y
      - pecl install sqlsrv
      - pecl install pdo_sqlsrv
      - docker-php-ext-enable sqlsrv
      - docker-php-ext-enable pdo_sqlsrv
      - rm -rf /var/lib/apt/lists/*

and env

DB_CONNECTION=sqlsrv
DB_HOST=mssql
DB_PORT=1433
DB_DATABASE=master // I just used the master database as it was for testing only 
DB_USERNAME=sa
DB_PASSWORD=he11oTHERE

@Sinnbeck Yeah, I’m on a Mac. I just installed Lando (never tried it before), will have a go and see if I can get it to work. :-)

@Sinnbeck Well, it seems to have mostly worked – it says it built the app correctly, except for one error: Unable to locate package msodbcsql17 (which I’m guessing would be fairly essential here). Looks like that’s because there’s no M1 version of that package yet. Will have to try on my desktop (which is x64) later.

@kokoshneta ah too bad. Sadly I don't have a mac to test on. :)

@Sinnbeck Since I’ve now managed (with your help) to get native PDO to connect to my remote database, I thought I’d revisit this and see if there was any difference there… and to my astonishment, my dates are now returned correctly, even from within Laravel!

I have no idea how this has happened.

I’ve done a composer update which has taken my version of Laravel from 9.3.1 to 9.8; and my local version of PHP has been updated from 8.1.4 to 8.1.5 with a Homebrew update recently. But apart from that, I haven’t changed anything. So either this was somehow fixed (perhaps accidentally) in Laravel itself, or the universe has decided it’s bothered me enough about date columns…

@kokoshneta very weird indeed, but yeah sounds like some obscure but in a minor version of something. But great to hear that it's now working