How to fix error "Conversion failed when converting datetime from character string" in SQL Query?

Question

The error that I found at the log is the one below.

'Illuminate\Database\QueryException' with message 'SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (SQL:

SELECT COUNT(*) AS aggregate 
FROM [mytable] 
WHERE [mytable].[deleted_at] IS NULL 
    AND [created_at] BETWEEN '2015-09-30T00:00:00' AND '2015-09-30T23:59:59' 
    AND ((SELECT COUNT(*) FROM [mytable_translation] 
         WHERE [mytable_translation].[item_id] = [mytable].[id]) >= 1)

)' in wwwroot\myproject\vendor\laravel\framework\src\Illuminate\Database\Connection.php:625

On the database, the DataType is datetime and is not null

Based on marc_s's answer I tried to change the format that I'm sending to the database. So I tried without the T on and [created_at] between '2015-09-30 00:00:00' and '2015-09-30 23:59:59'.

In my local, I'm using mysql, and the code works just fine. If I test the query above on the SQL Server client, both (with and without the T) works too.

How can I fix this problem without create any changes on the database itself?

The PHP/Laravel code:

$items = $items->whereBetween($key, ["'".$value_aux."T00:00:00'", "'".$value_aux."T23:59:59'"]);

Try: `{ts'1900-01-01 00:00:00'}` this format, like `between {ts '2015-09-30 00:00:00'} and ...` — Lukasz Szozda, Sep 09 '15 at 17:27
Sorry @lad2025 I don't know how I could implement this format (if you are talking about the YYYY-DD-MM is already being at use as the example from above) — Michel Ayres, Sep 09 '15 at 17:32
How `2015-09-30` is YYYY-MM-DD? Try also `SET DATEFORMAT YMD` — Lukasz Szozda, Sep 09 '15 at 17:35
@lad2025 (2015 == YYYY); (09 == MM); (30 == DD); last day of September of 2015 — Michel Ayres, Sep 09 '15 at 17:36
I talk about your `f you are talking about the YYYY-DD-MM is already being at use as the example from above)` — Lukasz Szozda, Sep 09 '15 at 17:38
@lad2025 I just said that in the piece of code `AND [created_at] BETWEEN '2015-09-30T00:00:00' AND '2015-09-30T23:59:59'` I'm using the format **YYYY-DD-MM** as you can see. And asked if it's not that what you suggested. If not, then how could I implement your suggestion, so I can bring the result? — Michel Ayres, Sep 09 '15 at 17:45
Before query place `SET DATEFORMAT YMD; your query;` And you will change for session dateformat. — Lukasz Szozda, Sep 09 '15 at 17:47
@lad2025 got it to work. based on the point of your comments. Thanks. I changed in the code part the format that I was passing. (just added to a variable before passing to the query. This way, I let the database decide the format that he wants) — Michel Ayres, Sep 09 '15 at 18:01

score 1 · Accepted Answer · answered Sep 09 '15 at 18:04

With @lad2025 help, I got it to work.

Based on the point of his comments on my question, I changed in the code part (Laravel/PHP in this case) the format that I was passing. (In reality, I "removed" the format it self, and just added fields to a variable before passing to the query. This way, I let the database decide the format that he wants)

Instead of

$itens = $itens->whereBetween($key, ["'".$value_aux."T00:00:00'", "'".$value_aux."T23:59:59'"]);

I changed the code to this:

$sta = $value_aux."T00:00:00";
$end = $value_aux."T23:59:59";
$itens = $itens->whereBetween($key, [$sta, $end]);

How to fix error "Conversion failed when converting datetime from character string" in SQL Query?

1 Answers1