how to insert an empty value in mysql date type field?

Question

How to insert a NULL or empty value in a mysql date type field (NULL = yes).

If I try to insert an empty value it inserts 0000-00-00 but I want to keep it empty or NULL.

Thanks for help.

UPDATE

Please see that I have set default value as NULL

  `payment_due_on` date DEFAULT NULL,

Ok OKies

I have got it working now

function get_mysqlDate($date, $delimiter='/') {
    if(check_empty($date)) {
        return 'NULL';
    }
    list($d, $m, $y) = explode($delimiter, $date);
    //$datetime = strtotime($date);
    return "'".date('Y-m-d', mktime(0,0,0,$m, $d, $y))."'";
}




"... SET mydate = ".get_mysqldate($_POST['mydate'])." ...."

Cheers

Are you actually inserting an empty String or really NULL? (or not specifing anything) Maybe the empty string is interpreted as 0000-00-00 — HerdplattenToni, Jul 16 '09 at 14:03
https://stackoverflow.com/a/72169592/2847436 this resolved my problem... — Mohammed Sufian, May 09 '22 at 09:22

chaos · Accepted Answer · 2009-07-16T13:43:50.897

25

If that's happening, it's because the column has been defined with NOT NULL or with a default '0000-00-00'. You need to change the column definition to allow NULLs and have no default.

Your insert should be specifying NULL, as well, not '', if that's what you mean by "empty value".

edited Jul 16 '09 at 13:43

answered Jul 16 '09 at 13:38

chaos

122,029
33
303
309

I have mentioned that I have set NULL = yes and default value as NULL as well. – TigerTiger Jul 16 '09 at 13:42
3

And you have NULL not 'NULL' in your statement? "INSERT ... VALUES (NULL, 1, 2)" – VolkerK Jul 16 '09 at 14:17

score 2 · Answer 2 · answered Feb 04 '10 at 03:45

phew.. was struggling with this null thing a bit.

I had a php script that copies, formats and stores data from one database A to database B. there was a date field in database A with NULL values. but when I copied it it became 0000-00-00 in my database B.

finally figure out it was because I was putting single quotes around NULL like ('NULL').

$started_date = mysql_result($databaseA['result'],$i,"start_date");

    if (empty($published_at_date)){
     $insert_sql .= "NULL,  ";
    } 
    else 
    {
     $insert_sql .= " '" . mysql_real_escape_string($started_date) ."', ";
    }

This fixed it.

score 0 · Answer 3 · answered Jul 16 '09 at 14:01

Just omit the field when your doing your insert.

Say your table is like this:

CREATE TABLE mytable(
`name` VARCHAR(30) NOT NULL,
`payment_due_on` date DEFAULT NULL);

If you want to insert something without the date, use:

INSERT INTO mytable (name) VALUES ('name here');

how to insert an empty value in mysql date type field?

3 Answers3

Linked