Zero Date converted to NULL

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
Galin Geshev
Posts: 3
Joined: Mon 30 May 2016 04:59

Zero Date converted to NULL

Post by Galin Geshev » 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?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Zero Date converted to NULL

Post by ViktorV » 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.

Galin Geshev
Posts: 3
Joined: Mon 30 May 2016 04:59

Re: Zero Date converted to NULL

Post by Galin Geshev » 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.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Zero Date converted to NULL

Post by ViktorV » 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.

Galin Geshev
Posts: 3
Joined: Mon 30 May 2016 04:59

Re: Zero Date converted to NULL

Post by Galin Geshev » Tue 31 May 2016 10:05

Thank you.

That would really help us users.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Zero Date converted to NULL

Post by ViktorV » 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.

Post Reply