SetJSONData and Date or DateTime fields

BIZ TMS XData
1

Are date and datetime (or timestamp) fields supported with the XDataWebDataset.SetJSONData function? I have fields of these types that seem to be in the JSON ok, but don't end up in the dataset for some reason. In the JSON they show up as YYYYMMDDTHHMMSS if that makes a difference.

This is JSON originally coming from FireDAC, so presumably a normal situation.

I work around it by pretending it is a string(15) field and the data flows through in that format. No errors are displayed so not sure what is happening.

2

TXDataWebDataset expects date values to be strings in ISO 8601 format, i.e., YYYY-MM-DDThh:mm:ss.sss. Can you please try such format?

3

Alright. I'd actually love to use that format everywhere. What I don't understand is what is changing it from that. I guess this isn't a SetJSONData issue but a FireDAC issue. Or even more likely an issue somewhere in my understanding of this.

I've got a date field from a DB2 database if that matters (same issues with datetime field). When I create an FDQuery in the IDE and leave it with the default FormatOptions, the query editor displays a date format of 'yyyy-mm-dd'. All good.

If I change the FDQuery's FormatOptions to something else, like yyyyMMMdd (on the options tab of the FDQuery), the query editor correctly displays that date format. Also all good.

However, everywhere else, including a DBGrid at designtime, a DBGrid at run-time, (field.AsString), etc. the yyyy-mm-dd format is used when displaying the data. Doesn't seem to matter what I do, other than overriding it in the field displayformat, it remains the same.

EXCEPT whenever I try to use the FireDAC savetostream function or the JSONWriter component, it ends up in yyyymmdd format. No clue as to why.

I've been fiddling with the (FDManager | FDConnection | FDQuery).FormatOptions.FormatDate values but nothing seems to have any effect. The only time I see a change is in the FDQuery's editor when viewing query results.

All I'm doing to get the data at runtime is something like
connection.create;
query.create;
query.savetostream();

I don't do anything with the fields - don't know/care how many or what types they are, just want it to output them. I'd have thought the default formatting should be used but nothing I do seems to change that default and I have no clue as to where the yyyymmdd format is introduced anywhere. JSONWriter has an option for DateTime but it defaults to ISO which should be yyyy-mm-ddThh:nn:ss. Super annoying.

Sorry, not really a WebCore / XData / TMS issue at this stage I suppose!

4

Muwahahaha.... cough ...haha..

So it is a JSON issue. In Delphi/source/data/firedac/FireDAC.Stan.StorageJSON.pas there's this little gem of a function that starts with:

procedure TFDJSONStorage.WriteValue(const APropName: String; APropIndex: Word;
  ADataType: TFDDataType; ABuff: Pointer; ALen: Cardinal);
const
  CDateTimeISO8601Format: String = '%.4d%.2d%.2dT%.2d%.2d%.2d';
  CTimeFormat: String = '%.2d%.2d%.2d';
  CDateFormat: String = '%.4d%.2d%.2d';

Changing it to:

procedure TFDJSONStorage.WriteValue(const APropName: String; APropIndex: Word;
  ADataType: TFDDataType; ABuff: Pointer; ALen: Cardinal);
const
  CDateTimeISO8601Format: String = '%.4d-%.2d-%.2dT%.2d:%.2d:%.2d';
  CTimeFormat: String = '%.2d:%.2d:%.2d';
  CDateFormat: String = '%.4d-%.2d-%.2d';

and then adding the FireDAC units to my project, rebuild and voila... JSON formatted with yyyy-MM-ddThh:nn:ss. Not so hard to do I suppose but took far too long to figure out how to do it!

5

Probably wouldn't be a bad idea for SetJSONData to support both date/time variations. Seems Delphi likes the "Basic" version vs. the human-readable "Extended" version of ISO8601. Makes some sense, given that JSON is less likely to be read by humans. But as they say, the great thing about standards is that there are so many to choose from :joy: In this case, there are variations even within the same standard!

6

Yes. Actually you are right, the standard specified the format without dots and colons, so it should supported indeed. We will improve that.