How to store NULL values in datetime fields in MySQL

Question

I have a  bill date  field that I want to be blank  NULL  until it s been billed  at which point the date will be entered   I see that MySQL does not like NULL values in datetime fields  Do any of you have a simple way to handle this  or am I forced to use the min date as a  NULL equivalent  and then check for that date   Thanks   EDITED TO ADD   Ok I do see that MySQL will accept the NULL value  but it won t accept it as a database update if I m updating the record using PHP   The variable name is  bill date but it won t leave the variable as NULL if I update a record without sending a value to  bill date -- I get this error    Database query failed  Incorrect datetime value     for column  bill date  at row 1   I assume I need to actually send the word NULL  or leave it out of the update query altogether  to avoid this error  Am I right  Thanks

User · Answer

For what it is worth   I was experiencing a similar issue trying to update a MySQL table via Perl   The update would fail when an empty string value  translated from a null value from a read from another platform  was passed to the date column   dtcol  in the code sample below    I was finally successful getting the data updated by using an IF statement embedded in my update statement                 my  stmnt  update tbl set colA   dtcol if      null    where colC         my  status  dbh- gt do  stmt undef  iref 1   iref 2   iref 2   ref 0

User · Answer

I just discovered that MySQL will take null provided the default for the field is null and I write specific if statements and leave off the quotes  This works for update as well   if empty  odate  AND empty  ddate        query2  UPDATE items SET odate null  ddate null  istatus   istatus  WHERE id   id

User · Answer

I just tested in MySQL v5 0 6 and the datetime column accepted null without issue

User · Answer

MySQL does allow NULL values for datetime fields  I just tested it   mysql gt  create table datetimetest  testcolumn datetime null default null   Query OK  0 rows affected  0 10 sec   mysql gt  insert into datetimetest  testcolumn  values  null   Query OK  1 row affected  0 00 sec   mysql gt  select   from datetimetest   ------------    testcolumn    ------------    NULL           ------------  1 row in set  0 00 sec    I m using this version   mysql gt  select version     -----------    version      -----------    5 0 45        -----------  1 row in set  0 03 sec    EDIT  1  I see in your edit that the error message you are getting in PHP indicates that you are passing an empty string  i e       not null  An empty string is different than null and is not a valid datetime value which is why you are getting that error message  You must pass the special sql keyword null if that s what you mean  Also  don t put any quotes around the word null  See my insert statement above for an example of how to insert null   EDIT  2  Are you using PDO  If so  when you bind your null param  make sure to use the  PDO  PARAM NULL  1  type when binding a null  See the answer to this stackoverflow question on how to properly insert null using PDO

User · Answer

This is a a sensible point   A null date is not a zero date  They may look the same  but they ain t  In mysql  a null date value is null  A zero date value is an empty string      and  0000-00-00 00 00 00   On a null date      where mydate        will fail  On an empty zero date      where mydate is null  will fail   But now let s get funky  In mysql dates  empty zero date are strictly the same   by example   select if myDate is null   null   myDate  as mydate from myTable where  myDate       select if myDate is null   null   myDate  as mydate from myTable where  myDate    0000-00-00 00 00 00    will BOTH output   0000-00-00 00 00 00   if you update myDate with    or  0000-00-00 00 00 00   both selects will still work the same   In php  the mysql null dates type will be respected with the standard mysql connector  and be real nulls   var     null  is null  var    Empty dates will always be represented as  0000-00-00 00 00 00    I strongly advise to use only null dates  OR only empty dates if you can   some systems will  use  virual  zero dates which are valid Gregorian dates  like 1970-01-01  linux  or 0001-01-01  oracle    empty dates are easier in php mysql  You don t have the  where field is null  to handle  However  you have to  manually  transform the  0000-00-00 00 00 00  date in    to display empty fields   to store or search you don t have special case to handle for zero dates  which is nice    Null dates need better care  you have to be careful when you insert or update to NOT add quotes around null  else a zero date will be inserted instead of null  which causes your standard data havoc  In search forms  you will need to handle cases like  and mydate is not null   and so on   Null dates are usually more work  but they much MUCH MUCH faster than zero dates for queries

User · Answer

Specifically relating to the error you re getting  you can t do something like this in PHP for a nullable field in MySQL    sql    INSERT INTO table  col1  col2  VALUES      col1          null          Because null in PHP will equate to an empty string which is not the same as a NULL value in MysQL  Instead you want to do this     sql    INSERT INTO table  col1  col2  VALUES      col1           is null  col2     NULL     col2          Of course you don t have to use is null but I figure that it demonstrates the point a little better  Probably safer to use empty   or something like that  And if  col2 happens to be a string which you would enclose in double quotes in the query  don t forget not to include those around the  NULL  string  otherwise it wont work   Hope that helps

User · Answer

I had this problem on windows   This is the solution   To pass    for NULL you should disable STRICT MODE  which is enabled by default on Windows installations   BTW It s funny to pass    for NULL  I don t know why they let this kind of behavior

User · Answer

It depends on how you declare your table   NULL would not be allowed in   create table MyTable  col1 datetime NOT NULL     But it would be allowed in   create table MyTable  col1 datetime NULL     The second is the default  so someone must ve actively decided that the column should not be nullable

[php] How to store NULL values in datetime fields in MySQL?

Examples related to php

Examples related to mysql

Examples related to datetime

Examples related to null