Laravel datetime incorrect format when saving to SQL Server

Question

I'm working on a project running on an existing database. The problem is that datetime columns are inserted using a wrong format in SQL Server. The server uses datetime as Y-m-d and data is being saved as Y-d-m.

I've made some tests, and when saving to MariaDB datetime is saved properly.

There are custom fields for updated_at and created_at, so they are declared on the model.

In the model

class NotaFaturamento extends Model
{
    const CREATED_AT = 'DT_CADASTRO';
    const UPDATED_AT = 'DT_ATUALIZACAO';

This is the QueryLog print after saving data. As you can see in the query log, datetime format is correctly parsed to SQL Server.

Querylog

On Config\app.php

'timezone' => 'America/Sao_Paulo',
'locale' => 'pt-BR',

Is this something that needs to be configured on SQLServer? I've searched a lot about this, but most of the responses are regarding SQL Server separators.

I also declared protected $dateFormat = 'Y-m-j h:i:s:000A'; on the model but the same problem happens. The issue is also present when storing Carbon objects.

Regards.

EDIT

As pointed out by Dan, the issue could be the DATEFORMAT on the SQL Server being used as DMY. Also, as pointed this issue and answered by @dns_nx, there is a workaround to manually change dateformat for saving on SQL Server.

I've added to my model

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

And any other date attributes on the model should be declared as being date:

protected $dates = ['DT_EMISSAO', 'DT_COMPETENCIA'];

I don't think this is the proper way to solve the issue, but it does work. And you could create another basemodel as mentioned by @dns_nx.

Regards

Datetime columns are an internal binary format in SQL Server. The formatted string you see when querying the data is formatted by the client application. — Dan Guzman
– Dan Guzman, Commented Oct 6, 2018 at 12:48
OK, I understand that, but since laravell/eloquent has the save() method built in it's system, shouldn't it make the proper conversions when saving to sql server? — Juliano
– Juliano, Commented Oct 11, 2018 at 16:59

Dan Guzman · Accepted Answer · 2018-10-13 11:51:00Z

I can't speak to Laravel/Eloquent specifically but a parameterized query with a strongly-typed datetime parameter will save the value properly. Since the value is not being saved correctly, it is because:

1) The actual parameter value provided for a datetime parameter type is wrong

2) The parameter type is (n)varchar with the value passed as a string that doesn't conform to ISO 8601 format

3) The parameter is passed as a string literal that doesn't conform to ISO 8601 format

For troubleshooting, run a SQL Trace (Extended Events or Profiler) on your dev database instance including batch_completed and rpc_completed events to capture the actual SQL queries. This will identify which of the above causes is the culprit. The rpc_completed will include both the parameter type and value. Be aware that with a datetime parameter type, the trace will always display the datetime value in YYYY-MM-DD hh:mm:ss.fff format, which is just a rendering of the actual binary value passed.

If the parameter type is (n)varchar, the non-ISO 8601 datetime string '2018-10-06 09:07:07.222' will be parsed by SQL Server using the current session DATEFORMAT setting. The default DATEFORMAT for a Portuguese language login is DMY but can be overridden by an explict SET DATEFORMAT command previously executed on the same session. With DATEFORMAT DMY and the string value '2018-10-06 09:07:07.222', the month and day portion will be parsed as month 6 day 10. Datetime literals are similarly parsed.

A quick search turned up this issue. Consequently, if you can't coerce a strongly-typed datetime to be passed by the application, a workaround may be to use datetime2(3) instead of datetime. SQL Server will parse datetime2 string '2018-10-06 09:07:07.222' as YYYY-MM-DD hh:mm:ss.fff regardless of the session DATEFORMAT setting. I recommend datetime2 for new development since it won't round the fraction seconds to 1/300 units and store greater precision values in less space.

Thanks for the info Dan. I've traced the Query and both created_at and updated_at fields were being parsed as ISO, but as you mentioned the DATEFORMAT for the database is set to DMY.

Collectives™ on Stack Overflow

Laravel datetime incorrect format when saving to SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related