[Last Call] Learn how to a build a cloud-first strategyRegister Now


Way to have MySQL Date field remain empty, no 0000-00-00

Posted on 2008-11-15
Medium Priority
Last Modified: 2013-12-13
I am attempting to have a date field remain empty when the form is processed and the data is inserted into the MySQL database.  I would like the date field to remain empty, instead of populating with 0000-00-00.

Is there a way to do this without changing the datatype of the field?
Question by:tru504187211
  • 3
  • 2
LVL 39

Accepted Solution

Roger Baklund earned 2000 total points
ID: 22969519
No, I don't think so. The 0000-00-00 is the internal "empty" for a NOT NULL date column. But you don't have to output this, you can change it in the select:
SELECT ... if(dateCol='0000-00-00','',dateCol) as dateCol, ... 
FROM ...

Open in new window


Author Comment

ID: 22969528
cxr:  Thanks for the reply.  I understand what you mean, partially about the if (datecol='0000-00-00'), but am unsure what could be done about it.  How could I go about doing...

display 'Today's Date instead'

...or somehow display today's date...
LVL 39

Expert Comment

by:Roger Baklund
ID: 22969542
That would be like this:
SELECT ... if(dateCol='0000-00-00',CURDATE(),dateCol) as dateCol, ... 
FROM ...

Open in new window

LVL 39

Expert Comment

by:Roger Baklund
ID: 22969547
The IF() function takes three parameters:  IF(<expression>,<true-output>,<false-output>)

Author Closing Comment

ID: 31517197
Got it to work!  Thanks for the assistance!!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question