Dates: 0 vs. NULL

[clintlord]

I am having a problem with the TOracleDataSet and dates. When I try to set a date parameter of the SQL to 0 (12/30/1899) it tries to write a NULL to the database. Unfortunatly the field is a "NOT NULL" and I need a 0 in the field to make it work correctly. Has anyone else had this problem? Has anyone else fixed it? What am I missing. Below is the actual code:

iOracleDataSet.DeclareVariable('PENDDATE', otDate);
iOracleDataSet.SetVariable('PENDDATE', 0);

Your help would be much appreciated.

 

[Marco Kalter]

The 0 date is currently treated as a null value. This is not correct of course (30/12/1899 is not null), and will probably be changed in the future.

It seems to me that you are also treating this 0 date as a special value. Can you use a different value, or perhaps also use the null value and make the column optional?

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 20 January 2000).]

 

[clintlord]

Thank you so much for your reply, I suspected that was the case. The problem I have is that oracle behaves strangely with null dates. Specifically when you do comparisons (e.g. where enddate

 

[Vents Lauva]

I think, You have 3 solutions with "smallest" date in column.

1st - declare column as
DATECOLUMN date default to_date('18991230','YYYYMMDD')

2nd - define variable in delphi like
ZeroDate := StrToDateTime('31.12.1899') (one day greatest then 0) and use this Zerovalue to assign date to date fields

3rd - use in SQL (enddate

 

[Marco Kalter]

Actually, 13-12-1899 is not the smallest date. I think 01-01-0001 is the smallest in Delphi. This translates to -693593 for a TDateTime. You can use this constant instead of 0 for your date comparison.

------------------
Marco Kalter
Allround Automations

 

[clintlord]

I'm sure that is true, but it really doesn't solve the problem. It is standard procedure (atleast in all the Delphi code that I have seen) to use 0 (12/30/1899) as the "no date" value. Like you've said before, DOA is doing it incorrectly. Are there any plans to fix it in the near future? It seems like that should be an easy enough fix. The only other solution that seems even plausable is the default setting on the column, but that seems like a bit of a kludge since I should be able to send a 12/30/1899 from Delphi (but can't).

 

[Simon Sakelsek]

I just stumbled upon the same problem myself. Is this problem all ready fixed in version 4.0.6.2?

Kind regards,
Simon Sakelsek

 

[Marco Kalter]

We have a TOracleSession.Preferences.ZeroDateIsNull property:

property ZeroDateIsNull: Boolean;

Determines if a TDateTime value of 0.0 is interpreted as Null. If you set this property to False, TDateTime values cannot be Null as 0.0 would correspond to 30/12/1899. To use date variables that can be Null you must use Variant values, which can of course be Null or Unassigned.