Insert NULL DateTime Field

[Fearpig]

Hello,

I'm having problems inserting a NULL value into an SQL DateTime field. The Field is set to allow Null values and any other value works correctly but when I try to leave the date blank on the form it insert 01/01/1970 00:00:00 - I'm guessing its an SQL baseline.

 

Here's is my code:

 

<?php 
$Person_ID = $_POST['Person_ID'];
$Course_ID = $_POST['Course_ID'];
$Confirmation_Date = $_POST['Confirmation_Date'];
$Training_Pack = $_POST['Training_Pack'];

$Time = "00:00:00";

//---Format Confirmation Date---

if ($Confirmation_Date == NULL){

$date_CD_formatted = NULL;

}else{

$date_CD = explode("/",$Confirmation_Date);
$date_CD_formatted = $date_CD[1]."/".$date_CD[0]."/".$date_CD[2]." ".$Time;

}

//----------UPDATE RECORD----------
$sql_Insert="UPDATE tbl_Attendees SET Confirmation_Date='$date_CD_formatted',Training_Pack='$Training_Pack, WHERE Person_ID='$Person_ID' AND Course_ID='$Course_ID'";
$Insert_Details=odbc_exec($conn,$sql_Insert);
if (!$Insert_Details)
{exit("Error in SQL");}
?>

 

Could it be that the form isn't passing a null value just an empty string? If so how would I account for this?

 

Any help would be appreciated.

[MasterACE14]

I think it would probably be easier just to insert the date. 

 

Regards ACE

[Fearpig]

Sorry MasterACE14 but I don't want a date in there.... that's the problem!

The field should be blank until I'm ready to put in a date.

[MasterACE14]

I got another noobie comment lol, what about deleting the date field?

 

Regards ACE

[Fearpig]

...? Sorry not quite sure what you mean.

 

The date is in a table and until there is a date in that field, I want that cell of the table to appear empty. The field will be used later I just want the option of having it blank.

 

I have a workaround that is to allow the date value of 01/01/1970 00:00:00 to be entered in the field but use the following code to not display it:

 

<?php
if ($Format_Confirmation_Date = 01/01/1970){
$Format_Confirmation_Date = "";
}
?>

 

...but this is not ideal as I now have to account for the incorrect values when reporting on the data.

[MasterACE14]

ah ok, I see what you mean. well i'm not exactly sure why it won't put in nothing when its suppose to. but I reccomend you download Turbo Database Admin, its much better then PHPMyAdmin. http://www.turbodbadmin.com/

 

see if that helps, PHPMyAdmin might not of set that field to NULL even though it says it has, install turbo database admin and have a look if it says the same.

 

Regards ACE