Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql allow invalid dates on select

Tags:

sql

mysql

For some reason, I can't even select invalid dates in my table. How do I force select? I just receive:

select * from table
>> Mysql2::Error: Invalid date: 1900-00-00

I'm not trying to insert, just select. Can I set allow invalid dates in select query?

mysql --version
mysql  Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i686) using readline 6.1

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
like image 777
Damien Roche Avatar asked Oct 17 '12 10:10

Damien Roche


People also ask

What is strict mode in MySQL?

Strict SQL Mode. Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE . A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range.

What is set sql_mode No_auto_value_on_zero?

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”; happens when different version of mysql is being used. When you are transfering from one server to another you should keep in mind the versions of database use in a new environment especially the mysql.


2 Answers

This is what I do to ignore invalid dates:

SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
like image 187
John Lee Avatar answered Sep 19 '22 13:09

John Lee


  • Log into mysql in the command line mysql -u root -p
  • Enter your password
  • View the current sql-modes using SELECT @@GLOBAL.sql_mode;
  • Copy the current modes (add or delete modes as needed) and paste in next step.
  • Set the sql-modes using SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES';
  • This adds ALLOW_INVALID_DATES and removes both NO_ZERO_DATE, NO_ZERO_IN_DATE
  • Restart the MySQL server /etc/init.d/mysql start
like image 31
Kirk Powell Avatar answered Sep 20 '22 13:09

Kirk Powell