Level 70
@marttyn It seems the error you are getting it's for soft deletion.
Just to double confirm, the soft delete column is nullable in the users migration file. For example:
$table->softDeletes();
``
Oct 30, 2023
13
Level 1
Conversion failed when converting date and/or time from character string
Hello, I'm using Laravel 9, PHP 8.0 and SQL Server. I'm trying to update a user but I'm getting 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.
The query running is:
select
count(*) as aggregate
from
[users]
where
[email] = user_email
and [id] <> user_id
and [deleted_at] = null
Where user_email and user_id are the email and id of the user being updated.
This isn't a query that I wrote so I'm assuming that is internal to Laravel. I know that the issue is because the query has wrong syntax, it should be "[deleted_at] IS NULL" instead of "= NULL", but as this is an internal query I don't know how to fix it.
Level 1
@tisuchi Thanks for the reply but yes, I have $table->softDeletes() in the users migration.
Level 75
Have you tried the db facade to see how it would work? Or straight PDO with getPdo()?
Level 1
@jlrdw Sorry I'm not sure what do you mean. Do you mean trying to run the query like this?
$pdo->prepare("SELECT * FROM users WHERE id = :id AND deleted_at IS NULL");
if so, when I try with "IS NULL" I get the user back but when I try with "= NULL" I get false as response.
Level 75
@Marttyn that's not the query from above.
Level 1
@jlrdw Yeah, it was just an example that I tested. I didn't wrote the original query, it's an internal query from Laravel.
Level 122
The query you posted looks like that used by the unique validation rule
Level 1
@Snapey I think you are right, I think it's from this validation.
'email' => 'required|email|string|max:255|unique:users,email,'.$this->id.',id,deleted_at,null',
if I remove deleted_at,null I don't get the error, but do you know why it's causing this error?
Level 75
@Marttyn I meant just try that same query in PDO to see if you get the error. Try both ways = null and IS NULL.
There could be an actual error in the framework code for sqlserver queries that need an issue raised.
Level 122
@Marttyn because deleted_at, null translates into a regular where statement
Laravel validation already handles trashed models.
Level 122
You can exclude trashed models;
'email' => [
'required',
'string',
'max:255',
Rule::unique('users')
->ignore($this->id)
->where(fn (Builder $query) => $query->whereNull('deleted_at' ))
],
Level 1
@Snapey Thank you, this worked.
Level 1
@jlrdw Now I tried with this query:
$smt = $pdo->prepare("SELECT count(*) as aggregate FROM users WHERE email = :email AND id <> :id AND deleted_at = NULL");
I tried both ways and the return was "aggregate" => "0", but I don't think the query with the "= NULL" worked, it's just misleading because of count.
If I try with the query:
SELECT * FROM users WHERE id = :id AND deleted_at = NULL
It returns false instead of the user but I don't get the exception message. Running the query with "= NULL" directly with PDO I don't get the exception, but I don't get the expected result either, but when Laravel runs the same query (because of the validation as pointed by Snapey), I get the exception, so I don't know where the issue is or if there's an issue even.
Please or to participate in this conversation.