Home > Software design >  Date format in RecordSet
Date format in RecordSet

Time:10-21

I need to store a date in a Recordset. I am doing it as follows:

rs: _Recordset;
dt: TDatetime;

Rs := CoRecordset.Create;
Rs.Fields.Append('Date', adDate, 4, adfldupdatable, Unassigned);    
Rs.Fields.Item['Date'].Value := FormatDateTime('dd/mm/yyyy', TDatetime)

But, in the Recordset the date appears in format d/m/yyyy

How can I store dd/mm/yyyy in the recordset?

CodePudding user response:

The adDate is not a string format, it is a date format. When you set value, it is performing a conversion from the string you entered into its internal format (which renders as d/m/yyyy when saved to a file).

If you want a specific string format for your date, use a string field to store the date. The disadvantage is that you are going to then need to make sure that you process the string field properly if you change your region settings to one where the date format is in M/D/Y order instead of what you stored as D/M/Y order.

CodePudding user response:

I'm afraid I cannot get your code to function as you claim. In any case, it is clearly in error because the line

Rs.Fields.Item['Date'].Value := FormatDateTime('dd/mm/yyyy', TDatetime)

is wrong, because you are passing TDateTime as the second argument, not your dt variable you apparently intend.

So, instead, using Delphi 10.4.2. I have compiled and run this program on Win10

program RecordSetTest;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Variants, System.Win.ComObj, WinAPI.ActiveX, WinAPI.AdoInt;

var
  RS: _Recordset;
  DT: TDatetime;
begin
  CoInitialize(Nil);
  DT := Now;
  RS:= CoRecordset.Create;
  RS.Fields.Append('Date', adDate, 4, adfldupdatable, Unassigned);
  RS.Open('c:\temp\rsdata.xml', EmptyParam, adOpenStatic, adLockOptimistic, 0);

  RS.AddNew('Date', FormatDateTime('dd/MM/yyyy', DT));

  RS.Save('c:\temp\rsdata.xml', adPersistXML);

  RS := Nil;

  writeln('saved');
  readln;
end.

Note the call to AddNew, which is necessary to add a data record to the RecordSet file.

On my system, which has short and long date formats of dd/MM/yyyy and dd MMMM yyyy, the file written is as follows:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
  xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
  xmlns:rs='urn:schemas-microsoft-com:rowset'
  xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
  <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
    <s:AttributeType name='Date' rs:number='1' rs:write='true'>
      <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:precision='0' rs:fixedlength='true'
       rs:maybenull='false'/>
    </s:AttributeType>
    <s:extends type='rs:rowbase'/>
  </s:ElementType>
</s:Schema>
<rs:data>
  <z:row Date='2021-10-20T12:32:59'/>
</rs:data>
</xml>

Note the format of the date value

2021-10-20T12:32:59

despite the formatting of the date value in

      RS.AddNew('Date', FormatDateTime('dd/MM/yyyy', DT));
  • Related