Lazarus

Programming => Databases => Topic started by: johnmc on April 02, 2019, 12:08:44 am

Title: DateTime null value
Post by: johnmc on April 02, 2019, 12:08:44 am
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][+][-]
  1. 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][+][-]
  1. FieldDefs.Add('Date', ftDateTime, 0, False);
what does the True/False signify.

John
Title: Re: DateTime null value
Post by: lucamar on April 02, 2019, 12:46:54 am
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.

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

Quote
One other small point, I'm having trouble finding this, when defining the the field using
Code: Pascal  [Select][+][-]
  1. 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.

[...]
Title: Re: DateTime null value
Post by: johnmc on April 02, 2019, 11:46:25 am
Further experimentation gives the following results:
Code: Pascal  [Select][+][-]
  1. Fields[8].AsDateTime := StrToDateTime('2/01/0001');
  2. 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][+][-]
  1. Fields[8].AsDateTime := StrToDateTime('31/01/0001');
Gives 31/01/0001 in the DBGrid cell.

John
Title: Re: DateTime null value
Post by: Josh on April 02, 2019, 11:54:35 am
Hi

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

I could be wrong though, just from memory

Title: Re: DateTime null value
Post by: johnmc on April 02, 2019, 12:00:15 pm
SEE Topic Add Method Parameters

Quote
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.
    [...]

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][+][-]
  1. with WineDBf do
  2.     begin
  3.       TableLevel := 7;
  4.       Exclusive := True;
  5.       FieldDefs.Add('ID', ftAutoInc, 0, True);  // Field[0]
  6.       FieldDefs.Add('WineID', ftInteger, 0, True);
  7.       FieldDefs.Add('WineName', ftString, 60, True);
  8.       FieldDefs.Add('Vintage', ftString, 4, True);
  9.       FieldDefs.Add('Quantity', ftInteger, 0, True);
  10.       FieldDefs.Add('Size', ftString, 12, True);
  11.       FieldDefs.Add('Region', ftString, 25, True);
  12.       FieldDefs.Add('Country', ftString, 25, True);
  13.       FieldDefs.Add('Date', ftDateTime, 0, True);  // Field[8]
  14.       FieldDefs.Add('NoteID', ftInteger, 0, True);
  15.       CreateTable;
  16.       Open;
  17.  
  18.       // Add data here
  19.       Append;
  20.       Fields[1].AsInteger := 1;
  21.       Fields[2].AsString := 'Ch. Smith Haut Lafitte';
  22.       Fields[3].AsString := '1988';
  23.       Fields[6].AsString := 'Bordeaux';
  24.       Fields[7].AsString := 'France';
  25.       Fields[4].AsInteger := 0;
  26.       Fields[5].AsString := 'Bottle';
  27.       //Fields[8].AsDateTime := StrToDateTime('10/2/1991');
  28.       Fields[9].AsInteger := 0;
  29.       Post;
  30.  
  31.  
  32.       AddIndex('idxByID', 'ID', [ixPrimary, ixUnique]);
  33.       AddIndex('idxByWineID', 'WineID', [ixUnique]);
  34.       AddIndex('idxByCountry', 'Country', [ixCaseInsensitive]);
  35.       AddIndex('idxByName', 'WineName', [ixCaseInsensitive]);
  36.       AddIndex('idxByNameRev', 'WineName', [ixDescending, ixCaseInsensitive]);
  37.     end;
Title: Re: DateTime null value
Post by: johnmc on April 02, 2019, 12:19:17 pm
Hi

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)


Title: Re: DateTime null value
Post by: Zvoni on April 02, 2019, 12:32:53 pm
From: http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm
Quote
D    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 * 1000L
Beware: In those olden times a Long was 4 Bytes signed integer
Title: Re: DateTime null value
Post by: lucamar on April 02, 2019, 02:31:34 pm
When I said:
Let 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.
Title: Re: DateTime null value
Post by: johnmc on April 02, 2019, 02:56:09 pm
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
Title: Re: DateTime null value
Post by: wp on April 02, 2019, 03:23:15 pm
Is there a null value for ftDateTime field.

I tried the following but it doesn't work.
Code: Pascal  [Select][+][-]
  1. Fields[8].AsDateTime := StrToDateTime('00/00/0000');

Did you try this?
Code: Pascal  [Select][+][-]
  1.    Dbf1.Fields[8] := null;    
Title: Re: DateTime null value
Post by: johnmc on April 02, 2019, 03:51:42 pm
That gives a compiler error:
Quote
cwdbmain.pas(91,24) Error: Incompatible type for arg no. 2: Got "Variant", expected "TField"

Title: Re: DateTime null value
Post by: ttomas on April 02, 2019, 04:19:31 pm
On real SQL RDBMS
Code: Pascal  [Select][+][-]
  1. Dbf1.Fields[8].AsVariant := null;
  2.  
Don't know about dbf
Title: Re: DateTime null value
Post by: wp on April 02, 2019, 05:00:12 pm
That gives a compiler error:
Quote
cwdbmain.pas(91,24) Error: Incompatible type for arg no. 2: Got "Variant", expected "TField"

Sorry, these damn typos... It should be
Code: Pascal  [Select][+][-]
  1.  Dbf1.FieldByName('Date').Value := null;
  2. // or
  3.  Dbf1.FieldByName('Date').AsVariant := null;
Title: Re: DateTime null value
Post by: lucamar on April 02, 2019, 05:04:48 pm
Code: Pascal  [Select][+][-]
  1.  Dbf1.FieldByName('Date').Value := null;
  2. // or
  3.  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?
Title: Re: DateTime null value
Post by: johnmc on April 02, 2019, 05:22:09 pm
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
Title: Re: DateTime null value
Post by: johnmc on April 02, 2019, 06:58:25 pm
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][+][-]
  1. WineDBF.FieldByName('Date').AsString
returns a null string, whereas
Code: Pascal  [Select][+][-]
  1. datetostr(WineDBF.FieldByName('Date').AsDateTime
returns a string of a date, 30/12/1899.

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

Any thoughts.
TinyPortal © 2005-2018