Data inserted in MYSQL even if it is in wrong format

Question

0.00/5 (No votes)

See more:

hello , i use mysql
when i insert wrong value in a date column
it inserted as 0000-00-00 instead of showing me an error
for example : insert 'hello' in birth date column

i need when i do a behavior like that and try to insert wrong values in date columns
i need my sql to show me error that tell me i am insert wrong data ,and insertion operation not happened .
i don't need to insert 0000-00-00
i try
set_mode='NO_ZERO_DATE'

but still same problem

Posted 15-Feb-15 2:30am

Hercal

Updated 15-Feb-15 2:31am

v2

Add a Solution

Comments

Kuthuparakkal 15-Feb-15 8:41am

What is the column data type in mysql table ?

Hercal 15-Feb-15 8:49am

datetime

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2015-02-15T03:10:00

According to the MySQL Documentation: [^]

Quote:
Be aware of certain properties of date value interpretation in MySQL:

MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the “:” delimiter, but is interpreted as the year '2010-11-12' if used in a date context. The value '10:45:15' is converted to '0000-00-00' because '45' is not a legal month.

As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.7, “Server SQL Modes”, for more information.

Visit Server SQL Modes[^] to find out how to set to strict mode.
However, if the query is coming from an application, the date validation should be done at the coding side. Or you may consider using a datapicker.