Search code examples
sqlconcatenationmysql-error-1292

SQL - MariaDB : SQL Error 1292 Truncated incorrect DOUBLE value

after few search with my buddy G, I found a lot of solution, but no one corresponds to my situation. Quick explanation : I am currently trying to create a "test table" which will receive 4 millions lines per day. That's around 48 lines/seconds.

To simulate this situation, I want to create a default number for my "LotNumber" column, which will being create based on hour and minutes. Exemple : all the lines created at 9AM and 45min will have the following "LotNumber" : W11409:451

a.k.a W114+HH:MM+1

So, my default column value is :

concat(cast('W114' as char charset utf8mb4) + left(cast(curtime() as char charset utf8mb4),5) + cast('1' as char charset utf8mb4))

And hell yes, that's barbaric. My "LotNumber" column is VARCHAR,

When I create a single line, HeidiSQL send error message 1292.

I am new to SQL, and I have no idea where my error is.

Solution

  • Your code works, just separate the data elements by , instead of +

    select concat(
  cast('W114' as char charset utf8mb4),
  left(cast(curtime() as char charset utf8mb4),5),
  cast('1' as char charset utf8mb4)
  )

    You can test on this db<>fiddle