DateTime null value
lazarus / 16-Databases

----------------------------------------------------------------------
johnmc · 2019-04-01 22:08

Is there a null value for ftDateTime field. I think in MySQL you can
insert something like 00-00-0000 00:00:00 is there an equivalent for
TDbf.

I tried the following but it doesn't work.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Fields[8].AsDateTime := StrToDateTime('00/00/0000');
One other small point, I'm having trouble finding this, when defining
the the field using
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FieldDefs.Add('Date', ftDateTime, 0, False);what does the True/False
signify.

John

----------------------------------------------------------------------
lucamar · 2019-04-01 22:46

Quote from: johnmc on April 02, 2019, 12:08:44 amIs there a null value
for ftDateTime field. I think in MySQL you can insert something like
00-00-0000 00:00:00 is there an equivalent for TDbf.

Let me a little time to look it up in my old dBase manuals, OK?

QuoteOne other small point, I'm having trouble finding this, when
defining the the field using
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FieldDefs.Add('Date', ftDateTime, 0, False);what does the True/False
signify.

It's in the documentation (and help files) for the FCL:
Quote from: Reference for unit 'db' (#fcl)TFieldDefs.Add : Add a new
field definition to the collection.
Declaration
Source position: db.pas line 211
[...]
procedure TFieldDefs.Add(
  const AName: string;
  ADataType: TFieldType;
  ASize: Word;
  ARequired: Boolean
); overload;
[...]

Arguments
AName
  Value for the Name property of the new item.
ADataType
  Value for the DataType property of the new item.
ASize
  Value for the Size property of the new item.
ARequired
  Value for the Required property of the new item.
[...]

----------------------------------------------------------------------
johnmc · 2019-04-02 09:46

Further experimentation gives the following results:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Fields[8].AsDateTime :=
StrToDateTime('2/01/0001');Fields[8].AsDateTime :=
StrToDateTime('30/01/0001');Any day between 1 and 30 gives a blank
cell in my DBgrid but no exceptions.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Fields[8].AsDateTime := StrToDateTime('31/01/0001');Gives 31/01/0001
in the DBGrid cell.

John

----------------------------------------------------------------------
Josh · 2019-04-02 09:54

Hi

I thought the year range was 1000-9999 in MySql?

I could be wrong though, just from memory

----------------------------------------------------------------------
johnmc · 2019-04-02 10:00

SEE Topic Add Method Parameters

QuoteIt's in the documentation (and help files) for the FCL:
Quote from: Reference for unit 'db' (#fcl)

    TFieldDefs.Add : Add a new field definition to the collection.
    Declaration
    Source position: db.pas line 211
    [...]
    procedure TFieldDefs.Add(
      const AName: string;
      ADataType: TFieldType;
      ASize: Word;
      ARequired: Boolean
    ); overload;
    [...]

    Arguments
    AName
      Value for the Name property of the new item.
    ADataType
      Value for the DataType property of the new item.
    ASize
      Value for the Size property of the new item.
    ARequired
      Value for the Required property of the new item.
    [...]
I interpreted that as meaning when True the field must be entered for
the record to be written into the dataset, with an exception if not
present. So the following should raise an exception but does not.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i with
WineDBf do    begin      TableLevel := 7;      Exclusive := True;    
FieldDefs.Add('ID', ftAutoInc, 0, True);  // Field[0]     
FieldDefs.Add('WineID', ftInteger, 0, True);     
FieldDefs.Add('WineName', ftString, 60, True);     
FieldDefs.Add('Vintage', ftString, 4, True);     
FieldDefs.Add('Quantity', ftInteger, 0, True);     
FieldDefs.Add('Size', ftString, 12, True);     
FieldDefs.Add('Region', ftString, 25, True);     
FieldDefs.Add('Country', ftString, 25, True);     
FieldDefs.Add('Date', ftDateTime, 0, True);  // Field[8]     
FieldDefs.Add('NoteID', ftInteger, 0, True);      CreateTable;     
Open;       // Add data here      Append;      Fields[1].AsInteger :=
1;      Fields[2].AsString := 'Ch. Smith Haut Lafitte';     
Fields[3].AsString := '1988';      Fields[6].AsString := 'Bordeaux'; 
   Fields[7].AsString := 'France';      Fields[4].AsInteger := 0;    
Fields[5].AsString := 'Bottle';      //Fields[8].AsDateTime :=
StrToDateTime('10/2/1991');      Fields[9].AsInteger := 0;     
Post;        AddIndex('idxByID', 'ID', [ixPrimary, ixUnique]);     
AddIndex('idxByWineID', 'WineID', [ixUnique]);     
AddIndex('idxByCountry', 'Country', [ixCaseInsensitive]);     
AddIndex('idxByName', 'WineName', [ixCaseInsensitive]);     
AddIndex('idxByNameRev', 'WineName', [ixDescending,
ixCaseInsensitive]);    end;

----------------------------------------------------------------------
johnmc · 2019-04-02 10:19

Quote from: josh on April 02, 2019, 11:54:35 amHi

I thought the year range was 1000-9999 in MySql?

I could be wrong though, just from memory

I think you are right but when creating the table you can use
something like
CREATE TABLE mytable (
myfirstfield SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
mydatefield DATETIME DEFAULT '0000-00-00 00:00:00)

----------------------------------------------------------------------
Zvoni · 2019-04-02 10:32

From: http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm
QuoteD    Date    8 bytes - date stored as a string in the format
YYYYMMDD

EDIT: IIRC, a workaround was opening/importing a DBF to Excel or
Access (not sure, i think it's been over 20 years i had any contact
with dbase).
There you would see, waht a Date-Field in dBase is really.
But the Description of a String in YYYYMMDD is pretty clear to me....
So, a NULL-Date would actually be an empty string or just '00000000'

EDIT2: With your Add of a Field-Def with Type ftDateTime you might
actually create a field with Type "@" (see my Link above)
Quote@    Timestamp    8 bytes - two longs, first for date, second for
time.  The date is the number of days since  01/01/4713 BC. Time is
hours * 3600000L + minutes * 60000L + Seconds * 1000LBeware: In those
olden times a Long was 4 Bytes signed integer

----------------------------------------------------------------------
lucamar · 2019-04-02 12:31

When I said: Quote from: lucamar on April 02, 2019, 12:46:54 amLet me
a little time to look it up in my old dBase manuals, OK?

I didn't though it would be so difficult but after some hours of
searching I've been unable to find what the "null" value for date
fields is. I've only found that there is indeed one, but what it is
seems to be a complete mistery.

Is it very important for you? I could install one of my dBase's (say a
dBase iV 2.0) in a DOSBox and try to see what it stores as a "null"
date (I suspect 8 consecutve spaces) but if it's not very important
I'd prefer not to waste so much time. Tell me if you really need it.

----------------------------------------------------------------------
johnmc · 2019-04-02 12:56

Thanks for the offer Lucamar but not that critical. My orignal data is
in a spread sheet and I have written some convertions to pascal code,
I'm just trying to decide what to do about all the unknown dates in
the original spreadsheet. I will probably just convert missing dates
to '1/1/0001' as this shows up as a blank cell in the DBGrid. However
this could be a bug.

John

----------------------------------------------------------------------
wp · 2019-04-02 13:23

Quote from: johnmc on April 02, 2019, 12:08:44 amIs there a null value
for ftDateTime field.

I tried the following but it doesn't work.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Fields[8].AsDateTime := StrToDateTime('00/00/0000');
Did you try this?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
 Dbf1.Fields[8] := null;

----------------------------------------------------------------------
johnmc · 2019-04-02 13:51

That gives a compiler error:
Quotecwdbmain.pas(91,24) Error: Incompatible type for arg no. 2: Got
"Variant", expected "TField"

----------------------------------------------------------------------
ttomas · 2019-04-02 14:19

On real SQL RDBMS
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Dbf1.Fields[8].AsVariant := null;  Don't know about dbf

----------------------------------------------------------------------
wp · 2019-04-02 15:00

Quote from: johnmc on April 02, 2019, 03:51:42 pmThat gives a compiler
error:
Quotecwdbmain.pas(91,24) Error: Incompatible type for arg no. 2: Got
"Variant", expected "TField"
Sorry, these damn typos... It should be
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 Dbf1.FieldByName('Date').Value := null;//
or Dbf1.FieldByName('Date').AsVariant := null;

----------------------------------------------------------------------
lucamar · 2019-04-02 15:04

Quote from: wp on April 02, 2019, 05:00:12 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 Dbf1.FieldByName('Date').Value := null;//
or Dbf1.FieldByName('Date').AsVariant := null;
For curiosity's sake (I really don't know): will that "null" be
converted to the proper null value for a DBF date field?

----------------------------------------------------------------------
johnmc · 2019-04-02 15:22

ttomas and wp This seems to work. The resultant .dbf file has 8 null
characters in the position in the file for the date field. The same
result is obtained by not writing that field to the dataset.

Whereas using 1/1/0001 does write some other characters, I assume the
coding for the date.

John

----------------------------------------------------------------------
johnmc · 2019-04-02 16:58

There is some strange behaviour with regard to dates in the range
1/1/0001 to 30/1/0001. If a date in this range or a null value is
entered then a DBGrid will show a blank cell, also
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
WineDBF.FieldByName('Date').AsStringreturns a null string, whereas
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
datetostr(WineDBF.FieldByName('Date').AsDateTimereturns a string of a
date, 30/12/1899.

Dates from 31/1/0001 seem to be handled correctly.

Any thoughts.