SQL Server datetime2 and datetimeoffset fields are not properly handled by Eloquent #1756

Closed
rochdev opened this Issue Jun 27, 2013 · 21 comments

Projects

None yet
@rochdev
rochdev commented Jun 27, 2013

When trying to use date mutators on SQL Server datetime2 or datetimeoffset fields, an exception is thrown.

The problem is that the date format returned by \Illuminate\Database\Eloquent\Model::getDateFormat() when using the SQL Server grammar is "Y-m-d H:i:s.000" which matches neither datetime2 nor datetimeoffset. I did not try datetime, but according to that format it should not even work either. Its use is being discouraged by Microsoft anyway, so support for datetime2 and datetimeoffset is crucial.

I have thought of the following solution:

The \Illuminate\Database\Query\Grammars\SqlServerGrammar::getDateFormat() method could return "Y-m-d H:i:s+" which would simply ignore anything after the seconds. While it generates a warning in the underlying DateTime object for any trailing characters (internally, so no worries about PHP warnings), Carbon ignores it. While this would properly handle both data types, international SQL Server versions might stay unhandled. A configuration could therefore be added (i.e. database.php) to override the default and completely resolve the issue.

I understand that this can be be done by extending \Illuminate\Database\Eloquent\Model or by overriding the method in each models, but it would be great to see it fixed directly at the framework level.

Let me know what you guys think.

@jonwhittlestone

Hi,

I would be interested in applying a fix to this because I'm using a SQL server (not by choice!) for my Laravel implementation.

@rochdev, I've tried editing getDateFormat() as you've suggested and I still get the error message. It goes from:

Unexpected data found. Unexpected data found. The separation symbol could not be found The format separator does not match Trailing data

to:

InvalidArgumentException Unexpected data found. Unexpected data found. The separation symbol could not be found

The Carbon functionality is being used on L4 Bootstrap

Thanks

Jon

@jonwhittlestone

Btw. I'm using SQL server version 2008 R2

@rochdev
rochdev commented Aug 16, 2013

@jonwhittlestone, As I said this solution alone (without any additional configuration option) works only when using US date formats in SQL Server. Are you using the US version of SQL Server? What is the format returned when doing a "SELECT SYSDATETIME()"?

For the record the function looks like this in my project:

protected function getDateFormat()
{
    return 'Y-m-d H:i:s+';
}

Also note that a final implementation of this would need to modify the SQL Server grammar, not the Eloquent model.

@taylorotwell
Member

Feel free to submit pull request for what needs to happen. I have no way to test SQL Server on my end.

@uxweb
Contributor
uxweb commented Jun 1, 2014

Is there any solution to this issue?, I'm having the same problem now in May 2014

@uxweb
Contributor
uxweb commented Jun 1, 2014

Hey, here are some updates.

Found that it is not Laravel neither Carbon fault.

This issue is related to [LU]nix systems.

If you are using freetds as the driver behind pdo, mssql or db_lib he is causing the date format.

According to freetds documentation in http://freetds.schemamania.org/userguide/locales.htm
says that in the config file locales.conf there are defined some formats to convert dates from SQL Server to the client.

I'm using Mac OS X and i have installed freetds in this location /usr/local/freetds, if you installed freetds using a package manager like aptitude, yum or whatever, maybe this file could be located at /etc/freetds or jut in /etc, anyway you can find it by "locate locales.conf".

The file contains this:

[default]
date format = %b %e %Y %I:%M:%S:%z%p

October 2015 UPDATE: To use the 24 hour format, it needs to be like this as @hodaddy mentioned:
date format = %b %e %Y %H:%M:%S:%z%p

[en_US]
date format = %b %e %Y %I:%M:%S:%z%p
language = us_english
charset = iso_1

[es_ES]
date format = %b %d %Y %I:%M%p
language = spanish
charset = iso_1

[pt_BR]
date format = %d/%m/%Y %H:%M
language = Portuguese
charset = iso_1

[it_IT]
date format = %d/%m/%Y %H:%M
language = Italiano
charset = iso_1

If you always want to get the right format no matter what, edit the [default]
section to this format:

[default]
date format = %Y-%m-%d %I:%M:%S

After doing this, restart apache or the php development server (php artisan serve).

And wow!! now it works!!

Hope this helps, Happy Coding!

@pknecht
pknecht commented Aug 24, 2014

+1 for rochdev his approach

@envision

This problem was here also. But our format is "2014-08-29 23:14:07.000".

It turned out that Unix dateformat DOESN'T HAVE MILLISECONDS. How convenient.

I came up with this "hack" as FreeTDS locale format:

%Y-%m-%d %H:%M:%S.000

Better ideas?

@pknecht
pknecht commented Aug 30, 2014

Just another thing to add that may help:

I tried to modify the created_at attribute in the BaseModel. You can then parse the attribute, cut it off or whatever you want to do.

class BaseModel extends Eloquent {

    public function getCreatedAtAttribute($attr) {       
        // do stuff with $attr  
        return $attr;
    }
    public function getUpdatedAtAttribute($attr) {        
        // do stuff with $attr
        return $attr;
    }

}
@nprokopic

I stumbled upon this same issue these days and here's my solution. Basically, I extended the Eloquent class in order to add some features I needed.

One of them is the ability to turn off the conversion of timestamps to Carbon instances (because of this little issue that we are having).

/**
 * Custom base model class
 */
class ExtendedEloquent extends Eloquent
{
    /**
     * Timestamps that are converted to carbon instances
     * @var Array
     */
    protected static $carbonFields = ['created_at', 'updated_at', 'deleted_at'];

    // more custom Eloquent code here... 

    /**
     * Disables the conversion of timestamps to Carbon instances
     * @return void
     */
    public static function disableCarbon()
    {
        MVMEloquent::$carbonFields = [];
    }

    /**
     * Enables the conversion of timestamps to Carbon instances
     * @return void
     */
    public static function enableCarbon()
    {
        MVMEloquent::$carbonFields = ['created_at', 'updated_at', 'deleted_at'];
    }

    /**
     * Gets the timestamps that will be converted to Carbon instances
     * @return Array
     */
    public function getDates()
    {
        return MVMEloquent::$carbonFields;
    }

    // more custom Eloquent code here...
}

Then, whenever you need it, just make the call:

ExtendedEloquent::disableCarbon();

// carbon is "turned off" for all models (that extend the above custom model)
// do something

ExtendedEloquent::enableCarbon();

// do something

You can make $carbonFields, enableCarbon() and disableCarbon() non-static as well and turn off Carbon conversion for the specific instances only. For me it was more convenient to turn it on/off for all models.

I hope it will help someone.

Cheers!

@Donool
Donool commented Feb 25, 2015

Addition to uxweb his solution:
if you are using datetime in SQL server and have a string like this 2015-02-20 08:58:37.000,
change the locales.conf (in /etc/freetds) so it looks like this:

[default]
date format = %Y-%m-%d %I:%M:%S.%z

and restart apache/httpd etc.

Now I can use {{$user->created_at}} again without any errors.

Thanks for the solution!

@hodaddy
hodaddy commented Apr 10, 2015

The reply from uxweb worked for me with one small change.
His format disregards am/pm.
I used this in my freetds locales.conf file to get a mysql datetime format with 24 hour time:

date format = %Y-%m-%d %H:%M:%S
@zamarrong

Hi i have the same problem, im using laravel 5 on ubuntu 14.04 with nginx and freetds but i have error with timestamps

@joleenshook

date format = %Y-%m-%d %H:%M:%S.%z
I definitely had to use this formula rather than date format = %Y-%m-%d %I:%M:%S.%z
Otherwise my times were all off by 12 hours.

@aalabarce

Same problem here on ubuntu 14.04, my articles table has the created_at column (datetime) with "9/3/2015 3:10:26 PM" and the servers SYSDATETIME() is "2015-09-17 13:15:58.9627500".

Any ideas?

@JeanLucEsser

The solution provided by @uxweb is the right one except for one major issue going forward:
Using locales.conf in FreeTDS seems to be deprecated.

The only solution that remains seems to override getDateFormat() in Laravel, but that shouldn't be the way of doing it. Unix based solutions like FreeTDS should allow the parsing ok the .milliseconds that seems to be at the heart of the issue.

Any comments?

@envision
envision commented Oct 2, 2015

@JeanLucEsser we have experienced issue with upgrading FreeTDS package, it breaks the remote connection. Your piece of information that locales.conf got deprecated must be the cause for that.

I would be greatly interested on another way to solve this than overriding Laravel / Eloquent native functionality.

@joleenshook

Do you have more information on "it got deprecated?". This is currently working for us, and looking at the installation documentation (http://www.freetds.org/userguide/locales.htm), it appears that while they may eventually drop it, it's still supported for date formatting. I am using this locales.conf on ubuntu 12.04 and 14.04 servers connecting to SQL Server 2012 and it works fine:

[default]
date format = %Y-%m-%d %H:%M:%S.%z

It would be nice to know that it's not going to break the next time I upgrade FreeTDS.

@loren138 loren138 added a commit to loren138/framework that referenced this issue Dec 7, 2015
@loren138 loren138 Update SqlServerGrammer - support microseconds
Taylor Requested a Pull Request to fix #1756
In short, this improves functionality but does not solve all problems.

Long Version:
This causes microsecond characters from SQL Server to be brought in.  This works for datetime fields which have 3 digits.  The old code assumed microseconds would be 0.  This code preserves them and doesn't break if they aren't 0.
This code, however, still does not work for datetime2 fields which have 7 digits of microseconds (or whatever a 7 digit second part is called) because PHP only supports 6 digits of precision.  I'm not sure how to have it ignore the last character without introducing extra symbols into the output date format.  For input `Y-m-d H:i:s.u+` works to ignore the last digit, but that introduces an unneeded `+` in output since the same format is used for input and output conversion.  Laravel creates datetime fields from eloquent so that reduces this issue, but datetime2 is the proper sql server field to use so people may run into it later.)

(Probably also referenced by: briannesbitt/Carbon#331)
6faf4f6
@skylei
skylei commented Sep 26, 2016

@taylorotwell
When I was using Laravel5.2, the time was not right:
For example:
MSSQL:
tsql result:
Id FeeName Creator Modifier CreateTime status
1 维修费用 赵* 赵* Sep 20 2016 07:51:19:000PM Sep 20 2016 07:54:27:000PM 1
2 维修费用 路* 路* Sep 26 2016 03:18:32:643PM Sep 26 2016 03:39:23:000PM 1
3 测试费用 赵* 赵* Sep 26 2016 03:26:04:810PM Sep 26 2016 07:21:29:000PM 1

The time in the example is not correct.

OS:CentOS
PHP:5.6.22 [Laravel 5.2.*]

@tlacaelelrl
tlacaelelrl commented Oct 28, 2016 edited

I have found myself setting up dev environments where the quickest method for me is to override a method in the sql grammar file located at

vendor/laravel/framework/src/Illuminate/Database/Query/Grammars/SqlServerGrammar.php
replaced this method

/**
 * Get the format for database stored dates.
 *
 * @return string
 */
public function getDateFormat()
{
    return 'Y-m-d H:i:s.000';
}

with this

/**
 * Get the format for database stored dates.
 *
 * @return string
 */
public function getDateFormat()
{
    return env('SYS_DATE_FORMAT', 'Y-m-d H:i:s.000');
}

This way I don't have to worry about setting up the dev locale, I just set the SYS_DATE_FORMAT whenever the dates do not work.

@jacksegal jacksegal added a commit to jacksegal/hounslow-madm that referenced this issue Dec 14, 2016
@jacksegal jacksegal Model Fix for SQL Server Dates 6c1a6f7
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment