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)

Answer 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.

Answer 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 ...