Devart Forums

Posted: **Mon 30 May 2016 05:25**

Hello,
we are using Ver 7.01 of "dbExpress driver for SQL Server" with "Embarcadero Delphi XE3".

I have a stored procedure defined in SQL Server with a single DATETIME parameter.
When I call that stored procedure passing the zero date value for the parameter ('30.12.1899') that value is transformed to NULL for the parameter when DevArt driver produces the final SQL script for the SQL Server.

Code: Select all

-- stored procedure definition --
ALTER PROCEDURE [dbo].[AAA](
   @BBB DATETIME
)

-- Delphi code--
-- declarations --
sqlcDevArt: TSQLConnection;
sp: TSQLStoredProc;
d: TDateTime;

-- code --
sqlcDevArt.Close;
d := EncodeDate(1899, 12, 30);
sp.ParamByName('BBB').Value := d;
sp.ExecProc;

Resulting script reaching the SQL server is

Code: Select all

exec [AAA] NULL

Why?
What I expect is:
exec [AAA] '30.12.1899'

The standard Delphi driver is behaving just as I expect.

Is this an optional behaviour of DevArt driver?

Posted: **Mon 30 May 2016 13:14**

Yes, indeed, when passing a null date value ('30.12.1899') to а DateTime parameter, the parameter is set to NULL. This behavior of dbExpress Driver for SQL Server is the standard behavior of our driver, that is used by many our users. So we don't see any reasons to change it.

Posted: **Tue 31 May 2016 06:48**

ViktorV wrote:Yes, indeed, when passing a null date value ('30.12.1899') to а DateTime parameter, the parameter is set to NULL. This behavior of dbExpress Driver for SQL Server is the standard behavior of our driver, that is used by many our users. So we don't see any reasons to change it.

Hello Viktor,
Honestly, your answer stressed me, I didn’t expected something like that. DevArt driver is great in all situations until now.
What you hard coded in this situation is nothing else but loss of information.
Should I explain to you that 0 (zero) and NULL is different? This is the first sentence in any DB theory book.
Why do you think Standard Embarcadero SQL Server driver (or any other driver) does not possess that convertion zero-to-null feature?
And something more. Reaching the brilliant idea for “zero date to null date” how you didn’t use it for INT datatype, or FLOAT datatype. What would be different in your mind? Why zero date is NULL data but zero INT is not converted to NULL INT?
Please, this is not serious.
At least you should provide a connection level parameter for making this behavior optional.
Please inform the society about your opinion and when can we expect the parameter.

Posted: **Tue 31 May 2016 09:28**

We will consider the possibility to add an option, that would allow not to set a DateTime parameter to NULL when passing a null date value ('30.12.1899') to it.

Posted: **Tue 31 May 2016 10:05**

Thank you.

That would really help us users.

Posted: **Fri 11 Nov 2016 09:05**

The new dbExpress driver for SQL Server 7.3.5 with an added option "NullForZeroDelphiDate", that would allow not to set the DateTime parameter to NULL when passing a null date value ('30.12.1899') to is available for download now.
The value of this option is set to True by default. If you want, while inserting null date value ('30.12.1899') the parameter not to be set to NULL, please set the "NullForZeroDelphiDate" option to False.

Devart Forums

Zero Date converted to NULL

Zero Date converted to NULL

Re: Zero Date converted to NULL

Re: Zero Date converted to NULL

Re: Zero Date converted to NULL

Re: Zero Date converted to NULL

Re: Zero Date converted to NULL