I am not sure what is this error!
#1292 - Truncated incorrect DOUBLE value:
I don’t have double value field or data!
I have wasted a whole hour trying to figure this out!
here is my query
INSERT INTO call_managment_system.contact_numbers
(account_id, contact_number, contact_extension, main_number, created_by)
SELECT
ac.account_id,
REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') AS Phone,
IFNULL(ta.ext, '') AS extention,
'1' AS MainNumber,
'2' AS created_by
FROM
cvsnumbers AS ta
INNER JOIN accounts AS ac ON ac.company_code = ta.company_code
WHERE
LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') ) = 10
here is my show create table for the table which the results are going into
CREATE TABLE `contact_numbers` (
`number_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) unsigned NOT NULL DEFAULT '0',
`person_id` int(11) NOT NULL DEFAULT '0',
`contact_number` char(15) NOT NULL,
`contact_extension` char(10) NOT NULL DEFAULT '',
`contact_type` enum('Primary','Direct','Cell','Fax','Home','Reception','Office','TollFree') NOT NULL DEFAULT 'Primary',
`contact_link` enum('Account','PDM','Other') NOT NULL DEFAULT 'Account',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0 = inactive, 1=active',
`main_number` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = main phone number',
`created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` int(11) NOT NULL,
`modified_on` datetime DEFAULT NULL,
`modified_by` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`number_id`),
KEY `account_id` (`account_id`),
KEY `person_id` (`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=534 DEFAULT CHARSET=utf8
MySQL error 1292 occurs if the syntax for the date is incorrectly entered.
Here at Bobcares, we have seen several causes for this error while troubleshooting MySQL issues as part of our Server Management Services for web hosts and online service providers.
Today we’ll take a look at the cause for this error and how to fix it.
Why does MySQL Error 1292 occur
Before we get into the solution part, let us first see what causes this error to occur.
This error normally occurs when the date is entered in an incorrect format. The date value like 0000-00-00 00:00:00 is not allowed with MySQL 5.7 version.
Also, this error can occur when trying to compare a number and a string in a WHERE or ON clause.
For instance, the error appears as below.
How we fix MySQL Error 1292
This error is of different types and can occur due to many reasons and also the solution will differ according to the error. Here are the different errors and the solutions that our Engineers provide to our customers.
1. If a field type is a DATE, then we make sure that the date is entered in the format “yyyy-mm-dd”.
2. Error Code: 1292 – Incorrect date value
Many of our customers use MySQL 5.7. But in this version date value like 0000-00-00 00:00:00 is not allowed. Hence, the above error occurs. In case, if our customers want to allow it, then we update their my.cnf like:
sudo nano /etc/mysql/my.cnfIn this file, we find
[mysqld]Then after that, we add the below line.
sql_mode=”NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”After adding the above line, we restart the MySQL service. For that, we run the below command.
sudo service mysql restart3. #1292 – Truncated incorrect DOUBLE value
Usually, this error message appears when customers try to compare a number and a string in a WHERE or ON clause.
So we make sure that they have similar declarations or convert the number to a string. Also, if we turn off strict mode, the error turns into a warning.
[Need any further assistance in fixing MySQL errors? – We’re available 24*7]
Conclusion
In short, this error can arise with different messages and has its own way to fix it. Today, we saw the resolution to this MySQL error.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
The MySQL error Truncated incorrect DOUBLE value is one of the weirdest errors in MySQL.
This is because the error can be caused by some mistakes in your SQL script that has nothing to do with a DOUBLE value.
The error is mostly triggered when there’s a mistake in UPDATE statements script.
Let’s see some example scripts that trigger the error. Suppose you have a database named students with the following data:
+----+---------+---------+-------+--------+-------------+
| id | name | subject | score | gender | student_id |
+----+---------+---------+-------+--------+-------------+
| 1 | Sarah | Math | 9 | male | 12937254892 |
| 2 | Natalia | Math | 8 | female | 08936A58421 |
| 3 | Christ | English | 4 | male | 87463X98107 |
+----+---------+---------+-------+--------+-------------+
One of the mistakes that could trigger the Truncated incorrect DOUBLE value error is when you use the AND clause when updating multiple columns of the table.
The script would look as follows:
UPDATE students
SET name = 'Sarah'
AND score = 9
WHERE id = '1';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Sarah'
While the error is because of the AND clause, the error description will make you think that there’s something wrong with the value 'Sarah'.
To fix the error, you need to replace the AND clause with a comma:
UPDATE students
SET name = 'Sarah',
score = 9
WHERE id = '1';
-- Query OK, 0 rows affected (0.00 sec)
-- Rows matched: 1 Changed: 0 Warnings: 0
Another thing that could trigger this error is if you try to compare a string value that has no number representation with a number value in the WHERE clause.
An example wrong statement could be as shown below:
UPDATE students
SET score = 5
WHERE student_id = 87463298107;
The error response would look as follows:
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '08936A58421'
The error above is because there’s an entry in the student_id table that has no equal number value representation.
The student_id column is a VARCHAR column that can contain a string type of alphanumeric characters or a number type of numeric characters.
When you create a comparison in the WHERE clause that uses the number type, then MySQL will try to convert the column’s string type to number type for the comparison.
In the case of our example, the second row of the student_id column has no equal number value representation:
+-------------+
| student_id |
+-------------+
| 12937254892 |
| 08936A58421 |
| 87463298107 |
+-------------+
The letter 'A' in the second row value causes MySQL unable to cast the value as an integer and do a comparison.
To fix the error, you need to wrap the value in the WHERE clause with quotation marks:
UPDATE students
SET score = 5
WHERE student_id = '87463298107';
-- Query OK, 0 rows affected (0.00 sec)
-- Rows matched: 1 Changed: 0 Warnings: 0
Interestingly, MySQL won’t throw the same error when you run a SELECT statement:
SELECT * FROM students
WHERE student_id = 87463298107;
The above query would return the result set without an error:
+----+--------+---------+-------+--------+-------------+
| id | name | subject | score | gender | student_id |
+----+--------+---------+-------+--------+-------------+
| 3 | Christ | English | 4 | male | 87463298107 |
+----+--------+---------+-------+--------+-------------+
And those are some SQL script mistakes that can trigger the Truncated incorrect DOUBLE value error.
As you can see, the error can be triggered even when you don’t have any column of DOUBLE type or a DOUBLE value in your scripts.
If you found this error and are unable to find what’s wrong with your statements, then I suggest you check if the types used by your columns are the same as the types in your statements.
If you’re using VARCHAR type in your column, then it’s better to compare the column value with a string even though it looks like a number.
When there are values of different types, you can explicitly convert one of the values to match the other using the CAST() function.
Good luck in fixing the error! 👍
When running the query below I keep getting a warning:
Warning | 1292 | Truncated incorrect DOUBLE value: ‘Resolved Date’.
I’m trying to extract only strings that are dates from str_customvalue. That is why I did a DATE(DATE(str_customvalue)) is not null.
Select
case str_category
when
('Resolved Date'
and (status = 'Closed')
and (DATE(str_customvalue) is not null)
)
then
cast(str_customvalue as datetime)
else cast(str_diff_date` as datetime)
end AS last_diff_date
From table
Is there any way to remove this warning? The query works fine, but in order for my script to pass QA I need to remove this warning.
hopper
13k7 gold badges49 silver badges52 bronze badges
asked Aug 15, 2013 at 20:24
0
I solved the issue by using IF() statement as such:
Select
IF ( str_category = 'Resolved Date' and
and (status = 'Closed')
and (DATE(str_customvalue) is not null,
cast(str_customvalue as datetime),
cast(str_diff_date` as datetime))
AS last_diff_date
From table
answered Aug 15, 2013 at 20:47
codeBarercodeBarer
2,1987 gold badges43 silver badges74 bronze badges
Приветствую. MYSQL начал страдать какой то фигней. У меня есть таблица:
CREATE TABLE `icsv_emails` (
`ID` int(11) NOT NULL,
`EMAIL` text COLLATE utf8_unicode_ci NOT NULL,
`MESSAGE_ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;Я делаю запрос SQL запрос
INSERT INTO `icsv_emails` SET `EMAIL` = "user@mail.ru" AND `MESSAGE_ID` = 1
И MYSQL мне возвращает:
Warning: #1292 Truncated incorrect DOUBLE value: ‘user@mail.ru’
Warning: #1364 Field ‘MESSAGE_ID’ doesn’t have a default value
Вставляет запись в итоге и там все равно 0
Я первый раз с таким сталкиваюсь, если честно
У кого какие идеи есть на этот счет?