Store NULL if date is empty

MySchizoBuddy · May 2007

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.

lech · May 2007

is the field type set as time/date? if so 0000-00-00 is the date equivalent of NULL.

MySchizoBuddy · 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

WallPhone · May 2007

Why not WHERE DateExpire > 0000-00-00 AND NOW() > DateExpire;?

MySchizoBuddy · May 2007

ok i'll do that. make the default 0000-00-00 and not NUll

MySchizoBuddy · May 2007

OK Date > 0000-00-00 doesn't work

jimw · 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.

Store NULL if date is empty

Comments