incorrect date value: '' 1

[simon551]

Hi,

I'm getting this error running on MySql 5.0.41 on an MS IIS machine
incorrect date value: '' for column 'beginningDate'

but don't get the error running on MySql 5.0.37 on XAMPP

I double-checked and the table structures are the same. The field is not required. Is there another way to insert a null string for a date other than ''?

This is my query:

INSERT INTO contract_lineitems (contractID, gbc, `description`, regAmt, `interval`, billingInstructions, beginningDate, endingDate, notes, retainer, dateCreated, editby) VALUES ('6', '6', '', '17520', 'Monthly', '', '', '', '', '1', Now(), '35')

 

[feherke]

Hi

Is there another way to insert a null string for a date other than ''?

Why not simply insert [tt]null[/tt] ? Or even more simple, do not insert anything in those fields.

Feherke.

http://rootshell.be/~feherke/

 

[elsenorjose]

Or even better, if the field is not needed, why not remove it altogether?

 

[simon551]

thanks. I'm using this in php. the script checks the code for POST data and does some formatting work on the dates if they are there. I can't just remove them. I have fixed it though to insert 'NULL' which seems to work. Thanks

 

[guelphdad]

note there is a significant difference between using the string 'NULL' as you have it above and NULL representing an absence of value. you should be using the latter.

 

[simon551]

I realize this is a mySql forum and not a php forum but since php is so commonly used in conjunction I wonder if anyone can help me further. I'm still having this problem.

if ( !($_POST['beginningDate']) == '') 
	{
		$date= ($_POST['beginningDate']);
		$timestamp = strtotime($date);
		$bdate = date("Y-m-d", $timestamp);
	}
	else 	{
			$bdate= 'NULL';
		}


$insertSQL="INSERT INTO table (beginningDate) VALUES ($bdate)";

This works to take care of the null, but if the date actually has a post value, then the query
rejects the date because it is not surrounded by quotes. What I'm struggling with is that MySql
requires either a NULL or a 'Value' but I don't really know how to accomodate the possibility of
either. Some have suggested that I leave the date out of the query if it is null. Would that mean
I would re-write the query in the if statement? Does anyone have a better way of dealing with this
problem?

 

[r937]

a better way of dealing with the problem?

there are only these options:

if the POST field contains a date, insert the date value with quotes, e.g. '2007-07-23', or without, e.g. 20070723

if there is no value, then you have a choice: omit that column from the INSERT statement altogether, or insert NULL with no quotes

r937.com | rudy.ca