null datetime value 30/12/1899 or 01/01/1800

Question

I have the following...

var
  LCnn: TADOConnection;
  qryGetData: TADOQuery;
begin
  ...
  //build a connection string to a SQL Server 2008
  ...
  qryGetData.Connection := LCnn;
  qryGetData.SQL.Text := 'SELECT * FROM MYTABLE'
  ...
  LDate := qryGetData.FieldByName('Date').AsDateTime; //Date its a datetime field in the table

end;

This works fine but, when "Date" field is NULL in some Pcs the LDate is 0 and in another is -36522.

Any idea??? Thanks!

edit:

the stranges behavior is

function TDateTimeField.GetAsDateTime: TDateTime;
begin
  if not GetValue(Result) then Result := 0;
end;

in the firts case, GetValue result is false so GetAsDateTime result is 0, in the second case GetValue result is true so return -36522 (01/01/1800)

Solution 1

TDateTime does not have a null value. Which means that if the database has null dates then your program is wrong. You need to accord such dates special treatment. Only call AsDateTime once you have determined that the field is not null. If you encounter a null field, you need to handle that in some special way, but you cannot put a value in a TDateTime that unambiguously means null since there is no such value.

Solution 2

Expanding on David's answer: One way to deal with the problem of NULL dates is to adapt your query so that it does not return NULL but an arbitrary date using the sql COALESCE statement.

qryGetData.SQL.Text := 
'SELECT Id, COALESCE(Date, '''1/1/1900 00:00:00'''), WhatEverFieldYouNeed FROM MYTABLE';
...
LDate := qryGetData.FieldByName('Date').AsDateTime;

This way you can check if the date's year is 1900 (which means that the DB value is NULL) and handle accordingly. Make sure that you choose the default date value wisely so that is does not fall in the range of expected dates in the database.

The second way is simply check if the value is really NULL and don't depend on implicit conversion.

if qryGetData.FieldByName('Date').IsNull then ...

Comments

[Agustin Seifert]

Yes, I agree with your point. But there is a reason why in some cases 0 or -36522 is returned. I'm trying to understand this behavior

[David Heffernan]

Any value that is returned when the field is null is wrong. Essentially it does not matter what value is returned since no meaningful value can be returned.

[whosrdaddy]

@AgustinSeifert: the point is that the value is NULL, any conversion of a NULL value is pointless

[David Heffernan]

+1 I like the COALESCE approach, so long as you can find a suitable sentinel date

[EProgrammerNotFound]

Where there is only one null condition to evaluate, ISNULL is a better approach then COALESCE (less overhead). However, The difference is almost insignificant

[Agustin Seifert]

Thanks for your answer but isnt what I need. I know how to avoid the problem but Im trying to find out why its happening. I have legacy code and NEED to know this behavior. But thanks anyway

[whosrdaddy]

Are you sure that the DB schema is EXACTLY the same? Could be some default value defined on the column?

[Sнаđошƒаӽ]

But still, may I know what exactly is returned by AsDateTime for null value? Yesterday I got your answer regarding return value of AsString when value is null. :-)

[David Heffernan]

Read the source code of the date time TField descendant to find our