CODEWITHSUNDEEP
mysql
Mangat Rai Modi
Mangat Rai Modi

Reputation: 5706

Mysql doesn't validate date field properly?

UPDATE2: Please don't correct statement the statement. Read question carefully!

I already new that the statement below is wrong. Please read the question properly!

Following query I noticed is working with MySql, where I expected an error or empty data due to wrong formatting -

select * from test where date='"2014-11-24';

Instead MySql is giving output for date=''. Is it expected or a bug?

I expected to give a syntax error(" not ended), or no data (searching whole date i.e. "2014-11-24 which won't exists).

UPDATE1: Guys, I know I have error in the syntax, and no It is not giving an empty row. I have some rows with date as empty(not null), like date=''. I am getting these entries as result!

Upvotes: 0

Views: 28

Answers (4)

Hanky Panky
Hanky Panky

Reputation: 46900

'"2014-11-24'
 ^

Is an invalid date. But the syntax has no issues therefore it computes to become a non existant date and then returns you result where there is no date, i.e. null.

'2014-11-24'

Is valid

This can also be confirmed with the following test

SELECT UNIX_TIMESTAMP('2014-11-24')    // valid value is returned

VS

SELECT UNIX_TIMESTAMP('"2014-11-24')    // 0 is returned because the date was invalid

Edit

Ok, seems like its still not clear for you. Take this example it will remove the confusion

SELECT UNIX_TIMESTAMP("")  // Empty string right? guess what; returns 0

That means for an invalid date the value computes to be 0, for an empty date it computes to be 0, for null it computes to be 0. 0=0=0 :) That is why you see the results where the date is empty in your table when you go for an invalid date.

Upvotes: 2

Ankit Gupta
Ankit Gupta

Reputation: 182

SELECT * FROM `test` WHERE Date = '2014-11-21'

Upvotes: -1

Balayesu Chilakalapudi
Balayesu Chilakalapudi

Reputation: 1406

The query searches for "2014-11-24 value in the test table. The record belongs to "2014-11-24 value won't be existed in the test table and hence empty value is returned.

Upvotes: 0

Edrich
Edrich

Reputation: 242

What is the purpose of double quote(") in your query?

'"2014-11-24'
 ^

Try this:

SELECT * FROM test WHERE date = '2014-11-24';

Upvotes: 0

Related Questions