[SOLVED] incorrect date value: ''

[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')

[fenway]

Why not insert NULL if it's null? v5 has strict mode enabled.

[simon551]

because I don't know how

 

This is my insert code:

you can see I'm trying 'NULL' and NULL.

 

Neither of these works

 

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

                       $gbc=$_POST['gbc'];
                       $description=$_POST['description'];
                       $interval=$_POST['interval'];
                       $billinginstructions=$_POST['billingInstructions'];
                       $notes=$_POST['notes'];
                       $retainer=GetSQLValueString(isset($_POST['retainer']) ? "true" : "", "defined","1","0");


                       

//strip commas from numbers
$number = $_POST['regAmt'];
$number = str_replace(",","",$number);
$cLId = $_POST['contractLineID'];
$empid=$_COOKIE['empid'];




$insertSQL="INSERT INTO contract_lineitems (contractID, gbc, `description`, regAmt, `interval`, billingInstructions, beginningDate, endingDate, notes, retainer, dateCreated, editby) VALUES ('$contract', '$gbc', '$description', '$number', '$interval', '$billinginstructions', '$bdate', '$edate', '$notes', '$retainer', Now(), '$empid')"; 
  mysql_select_db($database_conn_org, $conn_org);
//  $Result = mysql_query($insertSQL, $conn_org) or die(mysql_error());

echo $insertSQL;

[simon551]

nevermind. I think I got it by removing the quotes from the query and putting quotes around NULL in the variable. That is a pain though now for my site