Board index » delphi » Inserting Null date values

Inserting Null date values

Quote
"Richard Proudfoot" <richa...@acay.com.au> wrote:
>When there is no value sent to the stored procedure (ie the parameter of the
>TStoredProc is set to ''), the stored procedure inserts '01/01/1900'.

>How do I get the stored procedure to insert a NULL value for the date?

Richard,

Try this:
        MyStoredProc.ParamByname('MyDate').Value := null;
        MyStoredProc.ExecProc;

Phil Cain
--

 

Re:Inserting Null date values


You need to clear the parameter and then set a flag.. like so..

Param.Clear;
Param.Bound := True;

Read up on the TParam property Bound for explanation...

Quote
Richard Proudfoot <richa...@acay.com.au> wrote in message

news:87vce7$cr719@bornews.borland.com...
Quote
> D4, SQL Server 7

> I have a stored procedure which inserts a number of values into a table.
It
> receives the values from a TStoredProc. Some of the values are dates
> (shortdatetime), which have a table default of NULL.

> When there is no value sent to the stored procedure (ie the parameter of
the
> TStoredProc is set to ''), the stored procedure inserts '01/01/1900'.

> How do I get the stored procedure to insert a NULL value for the date?

> Thanks for your help,
> Richard

> ===================================
> CREATE PROCEDURE MyStoredProcedure
>  @MyDate1 smalldatetime,
>  @mydate2 smalldatetime,
>  @MyDate3 smalldatetime,
>  @etc

>  INSERT INTO MyTable(
>     MyDate1,
>     MyDate2,
>     MyDate3,
>     etc)
>   VALUES(
>    @MyDate1,
>     @MyDate2,
>     @Mydate3,
>     @etc)

Re:Inserting Null date values


Quote
Philip Cain <philc...@orelle.com> wrote in message

news:ma47aso74sc807k36laule161ebdqt2v00@4ax.com...

Quote
> "Richard Proudfoot" <richa...@acay.com.au> wrote:

> >When there is no value sent to the stored procedure (ie the parameter of
the
> >TStoredProc is set to ''), the stored procedure inserts '01/01/1900'.

> >How do I get the stored procedure to insert a NULL value for the date?

> Richard,

> Try this:
> MyStoredProc.ParamByname('MyDate').Value := null;
> MyStoredProc.ExecProc;

> Phil Cain
> --

Phil,

This should categorically not work.  I've never gotten it to work for SQL
databases.  See my other reply.

Re:Inserting Null date values


D4, SQL Server 7

I have a stored procedure which inserts a number of values into a table. It
receives the values from a TStoredProc. Some of the values are dates
(shortdatetime), which have a table default of NULL.

When there is no value sent to the stored procedure (ie the parameter of the
TStoredProc is set to ''), the stored procedure inserts '01/01/1900'.

How do I get the stored procedure to insert a NULL value for the date?

Thanks for your help,
Richard

===================================
CREATE PROCEDURE MyStoredProcedure
 @MyDate1 smalldatetime,
 @mydate2 smalldatetime,
 @MyDate3 smalldatetime,
 @etc

 INSERT INTO MyTable(
    MyDate1,
    MyDate2,
    MyDate3,
    etc)
  VALUES(
   @MyDate1,
    @MyDate2,
    @Mydate3,
    @etc)

Re:Inserting Null date values


How did you checked for "01/01/1900" value?

In BDE, when a date is null the AsDateTime method returns '01/01/1900' which
is the base for smalldatetime type. You need to check the property
Query.FieldByName('field').IsNull to see if the value is null, because the
type TDateTime is a double and can not handle null values.

Arthur

Richard Proudfoot escreveu na mensagem <87vce7$cr...@bornews.borland.com>...

Quote
>D4, SQL Server 7

>I have a stored procedure which inserts a number of values into a table. It
>receives the values from a TStoredProc. Some of the values are dates
>(shortdatetime), which have a table default of NULL.

>When there is no value sent to the stored procedure (ie the parameter of
the
>TStoredProc is set to ''), the stored procedure inserts '01/01/1900'.

>How do I get the stored procedure to insert a NULL value for the date?

>Thanks for your help,
>Richard

>===================================
>CREATE PROCEDURE MyStoredProcedure
> @MyDate1 smalldatetime,
> @mydate2 smalldatetime,
> @MyDate3 smalldatetime,
> @etc

> INSERT INTO MyTable(
>    MyDate1,
>    MyDate2,
>    MyDate3,
>    etc)
>  VALUES(
>   @MyDate1,
>    @MyDate2,
>    @Mydate3,
>    @etc)

Re:Inserting Null date values


Hi Philip!

On Thu, 10 Feb 2000 22:35:26 -0600, Philip Cain <philc...@orelle.com>
wrote:

Quote
>    MyStoredProc.ParamByname('MyDate').Value := null;

Better

MyStoredProc.ParamByname('MyDate').Clear;

tomi

Re:Inserting Null date values


Quote
"Perry Way" <nos...@nospam.com> wrote:
>Param.Clear;

You're right. Forgot about the clear method.

Phil
--

Re:Inserting Null date values


Arthur,

Firstly, I am using ADO, but the end result is the same in the database. To
answer your question, I am simply observing the result in the SQL Server
table.

Your point raises an interesting question.

To get round the problem, I simply avoided inputting a value to any date
fields where the date is not filled in on the form, but using 'Clear' is
obviously the proper way to handle this situation and that's what I will do.
Thanks guys for telling me what I should have been able to track down
myself!!

But in view of your point about TDateTime being double, I am interested to
understand what happens when you use Param.Clear.  What does it set the
TDateTime field to if not NULL?

Almost certainly a naive question, I know, but that's me!

Regards,
Richard

Re:Inserting Null date values


Perry,

Thanks for that. It was new to me.

Having read the help file, I am still unsure whether I should be setting
Bound to True or false. The help file says that 'when Bound is False, a
TStoredProc object attempts to supply a value directly from the server' -
does that mean that it leaves the server to supply  my default table value
of NULL?

This seems to conflict a bit with what went before...
'The Clear method will replace the value of the parameter with NULL, but
will not set Bound to False. If the Clear method is used to bind the
parameter to a NULL value, Bound must be separately set to True.'

I guess it depends on whether you want the TStoredProc or the Server default
to supply the NULL value. In view of Arthur Heinrich's comment  (see thread)
about not using NULL with a TDateTime field I am uncerain which is the
correct, best or only way to go?

Regards,
Richard

Other Threads