Inserting NULL into a smalldatetime

You'll probably all cry heretic or something when I tell that I'm
working with SQL Server databases, but I am. I actually don't have a
lot of choice in the matter. Changing it would mean telling a guy over
the phone how to install mySQL, so I'm trying to work SQL Server with
PHP.

I'm working with PHP version 4.3.1.0 and SQL Server 2000 housed on a
Win 2000 machine. I've decided the easiest for phone setup was to use
the ODBC function instead of trying to setup the mssql function...

To get to the point, I'm currently facing a problem with the
smalldatetime datatype. I can't INSERT a NULL value. The best I can get
so far is having 01/01/1900 00:00:00 AM show up.

The field is already set to accept NULL values and I can insert them
manually by creating a new line with nothing in the smalldatetime
fields or using ctrl+0, but when I try to do the following I get an
error:

$qryStr = "INSERT INTO OPSCenterISS (EventTypeID, EventStatus,
EventTitle, Location, AffectedUsers, EventDescription, StartDateTime,
EndDateTime, Contact, Organization, SubmitDateTime )
VALUES ( '$typeidinput', '$statusinput', '$titleinput', '$locinput',
'$affectedinput', '$descinput', '$startdatetime', 'NULL',
'$contactinput', '$orginput', '$submitdatetime' )"
or die ("Error in ODBC Write");
odbc_prepare ($sqlconnect, $qryStr);
odbc_exec($sqlconnect, $qryStr);
?>

It spits back an error that it can't convert to the smalldatetime
datatype. I think I might be using the wrong syntax, so it doesn't like
it. This error doesn't come up until I put that NULL in there. I have
other similar INSERT statements that use PHP variables alone and it
doesn't like that. Plus, if I try to set a PHP variable = NULL and
stick it in there, it gives me the same error.

Here's the error:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC SQL Server
Driver][SQL Server]Syntax
error converting character string to smalldatetime data type.,
SQL state 22008 in SQLExecDirect in D:\input.php on line 173

I know not many people work with SQL Server 2000 but I'm sure there's
someone out there. I just need to keep trying!

Re: Inserting NULL into a smalldatetime

Techie123 wrote:
> To get to the point, I'm currently facing a problem with the
> smalldatetime datatype. I can't INSERT a NULL value. The best I can
> get so far is having 01/01/1900 00:00:00 AM show up.
>
> The field is already set to accept NULL values and I can insert them
> manually by creating a new line with nothing in the smalldatetime
> fields or using ctrl+0, but when I try to do the following I get an
> error:

> > $qryStr = "INSERT INTO OPSCenterISS (EventTypeID, EventStatus,
> EventTitle, Location, AffectedUsers, EventDescription, StartDateTime,
> EndDateTime, Contact, Organization, SubmitDateTime )
> VALUES ( '$typeidinput', '$statusinput', '$titleinput', '$locinput',
> '$affectedinput', '$descinput', '$startdatetime', 'NULL',
> '$contactinput', '$orginput', '$submitdatetime' )"
> or die ("Error in ODBC Write");
> odbc_prepare ($sqlconnect, $qryStr);
> odbc_exec($sqlconnect, $qryStr);


What if you try to write NULL instead of 'NULL'.
Now you're trying to add the string 'NULL' to the database instead of a
NULL, which the database won't like if it's supposed to be a date format.

Grtz,
--
Rik Wasmus

Re: Inserting NULL into a smalldatetime

IT'S SO EASY I FEEL DUMB. Just completely omit INSERTing a NULL into it
all together and it automatically inserts one for you!!!!! This is a
really good group! I got my answer and I didn't even have to have a
reply! ;-)

P.S. - I tried the NULL instead of 'NULL' and it did the same thing.
Leaving it out worked for me.

Re: Inserting NULL into a smalldatetime

Perhaps I spoke too soon. I need to figure out what to do if I want to
have a function to erase a date previously entered.


Techie123 wrote:
> IT'S SO EASY I FEEL DUMB. Just completely omit INSERTing a NULL into it
> all together and it automatically inserts one for you!!!!! This is a
> really good group! I got my answer and I didn't even have to have a
> reply! ;-)
>
> P.S. - I tried the NULL instead of 'NULL' and it did the same thing.
> Leaving it out worked for me.

Re: Inserting NULL into a smalldatetime

OKAY, Here's the real story...
I tried the $variable=NULL, not $variable='NULL' and it worked, kind
of. Now I have 1/1/1900 in Enterprise Manager. When I print that out it
comes to 1900-01-01 00:00:00.

So, what's the answer here. That I do not know. I could just change all
my logic to look for that time stamp, but it seems stupid that I can
enter a NULL in manually, but there isn't some SQL command for it.

Techie123 wrote:
> Perhaps I spoke too soon. I need to figure out what to do if I want to
> have a function to erase a date previously entered.
>
>
> Techie123 wrote:
> > IT'S SO EASY I FEEL DUMB. Just completely omit INSERTing a NULL into it
> > all together and it automatically inserts one for you!!!!! This is a
> > really good group! I got my answer and I didn't even have to have a
> > reply! ;-)
> >
> > P.S. - I tried the NULL instead of 'NULL' and it did the same thing.
> > Leaving it out worked for me.

Re: Inserting NULL into a smalldatetime

Techie123 wrote:
> You'll probably all cry heretic or something when I tell that I'm
> working with SQL Server databases, but I am. I actually don't have a
> lot of choice in the matter. Changing it would mean telling a guy over
> the phone how to install mySQL, so I'm trying to work SQL Server with
> PHP.
>
> I'm working with PHP version 4.3.1.0 and SQL Server 2000 housed on a
> Win 2000 machine. I've decided the easiest for phone setup was to use
> the ODBC function instead of trying to setup the mssql function...
>
> To get to the point, I'm currently facing a problem with the
> smalldatetime datatype. I can't INSERT a NULL value. The best I can get
> so far is having 01/01/1900 00:00:00 AM show up.
>
> The field is already set to accept NULL values and I can insert them
> manually by creating a new line with nothing in the smalldatetime
> fields or using ctrl+0, but when I try to do the following I get an
> error:
>
> > $qryStr = "INSERT INTO OPSCenterISS (EventTypeID, EventStatus,
> EventTitle, Location, AffectedUsers, EventDescription, StartDateTime,
> EndDateTime, Contact, Organization, SubmitDateTime )
> VALUES ( '$typeidinput', '$statusinput', '$titleinput', '$locinput',
> '$affectedinput', '$descinput', '$startdatetime', 'NULL',
> '$contactinput', '$orginput', '$submitdatetime' )"
> or die ("Error in ODBC Write");
> odbc_prepare ($sqlconnect, $qryStr);
> odbc_exec($sqlconnect, $qryStr);
> ?>
>
> It spits back an error that it can't convert to the smalldatetime
> datatype. I think I might be using the wrong syntax, so it doesn't like
> it. This error doesn't come up until I put that NULL in there. I have
> other similar INSERT statements that use PHP variables alone and it
> doesn't like that. Plus, if I try to set a PHP variable = NULL and
> stick it in there, it gives me the same error.
>
> Here's the error:
>
> Warning: odbc_exec(): SQL error: [Microsoft][ODBC SQL Server
> Driver][SQL Server]Syntax
> error converting character string to smalldatetime data type.,
> SQL state 22008 in SQLExecDirect in D:\input.php on line 173
>
> I know not many people work with SQL Server 2000 but I'm sure there's
> someone out there. I just need to keep trying!
>
According to the manual, date/timedate fields can't be NULL, but only
0(zero) which equates to '0000-00-00' when outputted.