Keep date NULL when updating record

[_spaz]

Is it possible to keep a date field in a mysql NULL rather then it updating it to 0000-00-00 when i update other fields?  When I do a record update for other fields it takes the null values in the date fields and changes it to 0000-00-00 everytime.  How do I prevent that from changing and leaving it as NULL?

 

UPDATE table

SET Service='$Service',Provider='$provider',Date='$date'

WHERE id = '$id';

 

 

 

 

[rajivgonsalves]

the date field is mysql behaves a little different, from my experience with working with mysql dates I have never been able to keep it null it goes back to 0000-00-00 (I think this is mysql null for the date format) 

[DavidAM]

Acutally, unless they have changed something, you can set a DateTime field to NULL.  But, take a look at the example code:

 

UPDATE table
SET Service='$Service',Provider='$provider',Date='$date'
WHERE id = '$id';   

 

This is explicitly assigning a value to the Date column.  If $date is an empty string or 0 (zero) or NULL, you are still assigning a value to the column.  What would this look like if we assigned some values to those variables?

 

UPDATE table
SET Service='HardWork',Provider='LazyBoy',Date=''
WHERE id = '$id';   

 

Look at that, we are assigning an empty string to Date.  That is not a valid DateTime value.  And, according to the documentation, the server will set the column to that special "zero date" (0000-00-00 00:00:00).  If you want to set the column to NULL, you have to do so just like setting any other column to NULL:

 

UPDATE table
SET Service='HardWork',Provider='LazyBoy',Date=NULL
WHERE id = '$id';   

 

NO QUOTES around that.

[_spaz]

Thanks for the explanation....

Is there a way to add a statement into the query to say if date="" then default to NULL?

[rajivgonsalves]

my apologies you can set the column to null the above should work.

[DavidAM]

My first reaction was to use an IF statement as in :

 

UPDATE table
SET Service='$Service',Provider='$provider',Date=IF('$date' = '', NULL, '$date')
WHERE id = '$id';  

 

Which seems to work.  But only if $date returns an empty string.  I decided to try something a little more robust to see if we could set NULL if the date was invalid.  So I tried $date + INTERVAL 0 DAY, and strangely enought that returns NULL if $date is empty. I had expected it to return the zero date.  So, in MySql 4.1 (yes I know, I'm WAY behind the times here), the following should always set Date to NULL if $date does not contain a valid MySql date.

 

UPDATE table
SET Service='$Service',Provider='$provider',Date='$date' + INTERVAL 0 DAY
WHERE id = '$id';  

 

[_spaz]

My first reaction was to use an IF statement as in :

 

UPDATE table
SET Service='$Service',Provider='$provider',Date=IF('$date' = '', NULL, '$date')
WHERE id = '$id';  

 

Which seems to work.  But only if $date returns an empty string.  I decided to try something a little more robust to see if we could set NULL if the date was invalid.  So I tried $date + INTERVAL 0 DAY, and strangely enought that returns NULL if $date is empty. I had expected it to return the zero date.  So, in MySql 4.1 (yes I know, I'm WAY behind the times here), the following should always set Date to NULL if $date does not contain a valid MySql date.

 

UPDATE table
SET Service='$Service',Provider='$provider',Date='$date' + INTERVAL 0 DAY
WHERE id = '$id';  

 

You rock:) Works perfectly!!  Thanks again.

[fenway]

That's quite the hack -- I wouldn't be happy with that in my code.  Simply don't pass the date if it's not a date.

[DavidAM]

You can't always leave a column out of the update statement.  If there is currently a value in the database and the user (or the process) decides that it needs to be removed, then you have to set the column to NULL.  I don't know who decided that putting invalid data in the database (meaning a zero date) is better than throwing an error when a date is out of range, but IMHO it is a very bad idea.  The database is "doing us a favor" by allowing an insert or update to run, but we get stuck with data that is not valid.  So, instead of "SELECT * FROM schedule WHERE date IS NULL" we have to use "SELECT * FROM schedule WHERE date IS NULL OR Date = 0" (Note that you can't select where Date = '' (empty string) even though that was the way it was input).

 

From most of the code I've seen here, people like to have their SQL "pre-built" and just fill in the blanks.  Some use sprintf(), most seem to use the style our OP did here (UPDATE Table SET column1 = '$var1', column2 = '$var2' ...).  I've generally avoided both approaches, because: a) I don't like putting variables inside a string, it just feels wrong.  (I know that is a great feature of PHP, but I avoid it;) and b) it makes it impossible to set a value to NULL, the best you can get is an empty string.  (Which is invalid for a date, and is why we are in this topic in the first place). 

 

In the database world, NULL has a completely different meaning from an empty string (or zero'd integer).  NULL means there is no value (or we don't know the value); and an empty string means the value is known, it is nothing.  Sometimes you need a NULL.

 

The code (I suggested) is not pretty, but it provides a wordaround to the problem of the database "truncating" an invalid value and inserting something other than what was provided.  A better solution would be:

$sql = 'UPDATE table ' . 
'SET Service=' . (empty($Service) ? 'NULL' : "'$Service'), " . 
'Provider=' . (empty($provider) ? 'NULL' : "'$provider'," .
'Date=' . (empty($date) ? 'NULL' : "'$date' " . 
"WHERE id = '$id ';  "

 

which I accomplish using a couple of home-grown functions:

$sql = 'UPDATE table ' . 
'SET Service=' . quoteString($Service) . ', ' . 
'Provider=' . quoteString($provider) . ', ' .
'Date=' . quoteStringNull($date) . ' ' .
"WHERE id = '$id ';  "

 

quoteString() returns the supplied value, escaped and enclosed in single quotes. quoteStringNull does the same thing, except if the string is empty it returns the string NULL without the single quotes.

[fenway]

You get to decide how mysql interprets out-of-date values based on sql_mode -- if it's strict, it will barf.