Recent

Author Topic: DateTime null value  (Read 1363 times)

johnmc

  • New Member
  • *
  • Posts: 25
DateTime null value
« 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

lucamar

  • Hero Member
  • *****
  • Posts: 2120
Re: DateTime null value
« Reply #1 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.

[...]
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

johnmc

  • New Member
  • *
  • Posts: 25
Re: DateTime null value
« Reply #2 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

josh

  • Hero Member
  • *****
  • Posts: 754
Re: DateTime null value
« Reply #3 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

Development Installation Lazarus 1.3, FPC 2.7.1,Windows 7/8 32/64, OSX, *nix

Test Environment Lazarus & FPC Trunk on Windows and OSX (Cocoa Mainly on OSX). Testing also Crosscompile windows to OSX.. 
Any posts made from 2015 will be based on Lazarus Trunk.

johnmc

  • New Member
  • *
  • Posts: 25
Re: DateTime null value
« Reply #4 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;
« Last Edit: April 02, 2019, 06:24:19 pm by johnmc »

johnmc

  • New Member
  • *
  • Posts: 25
Re: DateTime null value
« Reply #5 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)



Zvoni

  • Sr. Member
  • ****
  • Posts: 295
Re: DateTime null value
« Reply #6 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
« Last Edit: April 02, 2019, 12:39:11 pm by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

lucamar

  • Hero Member
  • *****
  • Posts: 2120
Re: DateTime null value
« Reply #7 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.
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

johnmc

  • New Member
  • *
  • Posts: 25
Re: DateTime null value
« Reply #8 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

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: DateTime null value
« Reply #9 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;    
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

johnmc

  • New Member
  • *
  • Posts: 25
Re: DateTime null value
« Reply #10 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"


ttomas

  • Full Member
  • ***
  • Posts: 181
Re: DateTime null value
« Reply #11 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

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: DateTime null value
« Reply #12 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;
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

lucamar

  • Hero Member
  • *****
  • Posts: 2120
Re: DateTime null value
« Reply #13 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?
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

johnmc

  • New Member
  • *
  • Posts: 25
Re: DateTime null value
« Reply #14 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
« Last Edit: April 02, 2019, 05:29:06 pm by johnmc »