Question 275695 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Conversion failed when converting date and/or time from character string

I’m using Laravel 9 and PHP 8.0. When I try to update a user, I get the following exception:

SQLSTATE[22007]: [Microsoft][ODBC Driver 18 for SQL Server][SQL
Server]Conversion failed when converting date and/or time from
character string.

I think the query being executed is internal to Laravel:

SELECT
  count(*) AS aggregate
FROM
  [ users ]
WHERE
  [ email ] = user_email
  AND [ id ] <> user_id
  AND [ deleted_at ] = NULL

user_email and user_id are the email and id of the user being updated.

I know that the issue here is because the query has incorrect syntax for SQL Server, it should be "[ deleted_at ] IS NULL" instead of "= NULL", but I don’t know to fix it.

QueryException Stack Error

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks everyone. I was able to solve the issue thanks to Snapey at Laracast forum. The query was being executed because of the Laravel validation, but writing the validation like this generates the wrong query.

    'email' => 'required|email|string|max:255|unique:users,email,'.$this->id.',id,deleted_at,null',

    Changing it to this solves the issue:

    'email' => [
    'required',
    'email',
    'string',
    'max:255',
    Rule::unique('users')
            ->ignore($this->id)
            ->where(fn (Builder $query) => $query->whereNull('deleted_at' ))],

    I'm not sure if this is an issue with Laravel or if it's intended to work like this, but I think the simplified way should work as well and generate the right query.


  2. 0

    On the one hand, SQL Server does not allow filters of

    something = null

    to be used. On the other hand, you found a use-case when this is generated into the query.

    This is unacceptable and not only due to syntax error, but also due to logical error. MySQL allows you to compare things with null and will return null to you.

    Hence, you will need to contemplate on how you will resolve this. Possible solutions

    Fix the Laravel code

    This will ensure that your code is different from Laravel’s official code, which will get you into trouble if Laravel does not apply a similar fix, but you can reach out to Laravel’s team and tell them about the problem, they may hopefully fix it.

    Create a DB proxy

    You can write a CLI application that takes any command-line commands being sent to it, forwards it to SQL Server and when it gets the response, it sends it back to the issuer. You can implement such a proxy on your own or search for an existent part.

    This proxy could then search for

    = null

    in any received query and replace it with IS NULL.

    Use a view

    You could create a view for your users table for not deleted users and use this view when you are interested in not deleted users. Maybe you could have a BaseUser model class for common functionalities.

    Not using soft-delete

    You can also avoid using soft-delete and implement the same logic yourself.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search