Store NULL if date is empty

If date field is empty then store NULL in the database.
if (ForceIncomingString('DateExpire', '') == "") $sql->AddFieldNameValue('DateExpire', NULL); else $sql->AddFieldNameValue('DateExpire', FormatStringForDatabaseInput(FormatDateToMysql(ForceIncomingString('DateExpire', ''))));
this code isn't working. It doesn't store NULL it stores 0000-00-00 instead.
the default value of DateExpire Field is NULL.

Comments

  • is the field type set as time/date? if so 0000-00-00 is the date equivalent of NULL.
  • edited May 2007
    That fails UPDATE LUM_DISCUSSION SET Closed = 1 WHERE DateExpire IS NOT NULL AND NOW() > DateExpire;It has to be NULL. it picks up 000-00-00 as not null and runs the query
  • Why not WHERE DateExpire > 0000-00-00 AND NOW() > DateExpire;?
  • ok i'll do that. make the default 0000-00-00 and not NUll
  • OK Date > 0000-00-00 doesn't work
  • edited May 2007
    You could use the function "bool checkdate ( int $month, int $day, int $year )" after you split up your date into these parts.

    list($month, $day, $year, $hour, $minute, $second ) = sscanf($date, "%d/%d/%d %d:%d:%d");

    Wait, you could split the date and see what values are in the components for the null value. Then you could use that in your query.
This discussion has been closed.