MySQL 1292 Warning on varchar date value to number conversion

Question

I know why the warning occurs.

On a varchar(20) column set to '0000-00-00 00:00:00' format in MySQL which a numeric comparison such as:

select * from table where varchar_date_column > 0;

is performed... Then I will have warnings that say:

Warning | 1292 | Truncated incorrect DOUBLE value: '2011-03-16 06:17:04' |

So my question is: Is there a reason NOT to do it this way? Because it works in the program anyways.

@zerkms hi, yes I know this, I'm dealing with a decision that I didn't make. :3 If I remember correctly, it's the partial index on just the DATE() part of a datetime type that is wanted. — Paul Carlton, Mar 22 '11 at 02:43
Index on datetime works pretty good. Varchar as date is truly bad idea. No comparison, bigger column size and so on... — Victor Perov, Oct 09 '14 at 16:26

score 2 · Answer 1 · answered Mar 22 '11 at 21:56

2

Since you are storing a string, you should compare it to a string.

This should give you the same results, without the warning:

select * from table where varchar_date_column > '0';

answered Mar 22 '11 at 21:56

Ike Walker

Currently running a test on this. Will get back shortly. :) – Paul Carlton Apr 04 '11 at 23:05
ACTUALLY... does not work. Comparison does not work the same. Exact same query gives row set result with 0 and an empty set with '0'. – Paul Carlton Apr 04 '11 at 23:36

1 Answers1