MSSQL Default Date format is not universal

[tobya]

Laravel Version

10.4.1

PHP Version

8.1.4

Database Driver & Version

MS SQLSERVER

Description

Overview

Laravel uses the ISO standard international date format (YYYY-MM-DD)

'Y-m-d H:i:s.v';

framework/src/Illuminate/Database/Query/Grammars/SqlServerGrammar.php

Lines 506 to 509 in 3a46fa3

	public function getDateFormat()
	{
	return 'Y-m-d H:i:s.v';
	}

This is supposed to be standard and in most instances it is, however when using MSSQL SERVER it is not universal and is country and language dependent.

In SQL Server

2023-04-01 12:00:00

may be interpreted as the 4th of January or the 1st of April depending on your machine language and country settings.

This is discussed in several places online

• https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries
• https://stackoverflow.com/questions/19565320/why-is-sql-server-misinterpreting-this-iso-8601-format-date

There seem to be 1 real alternative that is guarantied to always be interpreted correctly.

• Ymd H:i:s.v

Changing the default format in the getDateFormat() method in the SQLServer Grammar solves this problem without side effects.

I will post a PR with this change.

--

Workaround

I have a large old existing application that I have integrated very successfully with Laravel in the last 2 years that has a very large code base using hand crafted SQL Statements as well as new code written using Models and Eloquent.

The only way for me to work with MSSQL and Laravel is to manually change the function above in the Illuminate framework. I do this with a Console command

https://github.com/tobya/BCSModel/blob/main/src/Console/CheckSQLGrammerDate.php

This is run automatically every time composer update runs and for my application it works very nicely . However I feel like it should be possible to make a fix in the framework since this is a real issue.

Please note it should not matter what I do or do not have my MSSQL language set to or any other setting, the ISO standard is very clear the format is

YYYY-MM-DD

The issue here is MSSQL Server and since the grammar in question is specifically for MSSQL server I think it makes sense to change it.

I raised a discussion issue about this earlier this year.

Steps To Reproduce

• Download SQLServer Express
• Install
• Set language to a country that uses a differnt date setting to your country. For example I dont need to change anything as language by default for MSSQL is set to english(american) which has a default date format of mdY whereas Ireland is dmY
• Create a basic laravel project with a model and table with a datetime field
• Create a new model and set the datetime field.
• An error will occur saying that a conversion of a varchar type to a datetime resulted in an out of range error.

[tobya]

My PR has been closed as obviously not good to add to a point release.

Should I post the PR to master so it would go in next larger release?

[driesvints]

Thank you. We've had many issues about date formats in MSSQL before. We won't ever change the default, sorry. It's best that you overwrite this method for your database engine.

[tobya]

Would it be possible to allow it be changed by config?

If I was to submit a PR that allowed it to be set by calling a method or via environmental values, might that be acceptable?

[tobya]

If anyone stumbles upon this and is interested in a solution, I have released a package that makes the change.

• https://github.com/tobya/laravel-mssql-dateformat