0 votes

Since Efficy 11.2, empty date fields (NULL values) are no longer converted to a blank string with dataSetToJson but are converted to string "1899-12-30T00:00:00.000Z"

This means that testing for a empty date is changed.

asked in WorkFlow / Serverscript by (7.4k points)

2 Answers

0 votes
Best answer

Hello Kristof,

This behaviour has been adjusted and improved for the final 11.2 release.

DatasetToJSON and DateToISO8601 now return just the date part when the time part is empty (for all dates), and for NULL dates, the value is therefore "1899-12-30".

As promised also there is a method isEmptyDate(TDateTime, CheckAll) that is provided to verify any ISO8601 date string against the possible empty date values, such as the Delphi zero date (30 dec 1899). if CheckAll is set to true, the method will also consider empty the SQL zero date 1 jan 1900 and the Unix zero date 1 jan 1970.

Example :

test = {
   '30/12/1899 always': isEmptyDate('1899-12-30'),
   '1899-12-30T00:00:00.000Z always': isEmptyDate('1899-12-30T00:00:00.000Z'),
   '1/1/1900 with checkAll=true': isEmptyDate('1900-01-01', true),
   '1/1/1970 with checkAll=true': isEmptyDate('1970-01-01T00:00:00.000Z', true)
answered by (449 points)
+1 vote

The dataSetToJson code in Efficy 11.2 is changed to respect the datatype. Date are always returned as dates, not a blank strings.

A code example explaining the change and also a way to detect a Null date using a new custom module.

@import dateHelpers from "custom/utils/datehelpers"
function main() {
    var sql = "select cast(null as date) as d from dual";
    var data = JSON.parse(dataSetToJson(Efficy.sqlQueryDataset(sql, ""), false));

    // Efficy 11.1
    Efficy.log("data: " + JSON.stringify(data)); // data: [{"D":""}]

    // Efficy 11.2
    Efficy.log("data: " + JSON.stringify(data)); // data: [{"D":"1899-12-30T00:00:00.000Z"}]

    // Test if date field is empty in Efficy 11.2
    if (dateHelpers.isJSONNullDate(data[0].D)) {
        Efficy.log("The date is empty");

The module custom/utils/datehelpers:

var module = {
     * Since 11.2, dataSetToJson converts empty dates to "1899-12-30T00:00:00.000Z"
     * This helper can detect empty dates
    isJSONNullDate: function (jsonDate) {
            // 11.2: Converts a ISO-8601 date-and-time value (ex: 2008-09-15T15:53:00+05:00) to a TDateTime value.  
        return yearOf(iso8601ToDateTime(jsonDate)) == 1899
return module;

Consider this as a FYI post

answered by (7.4k points)
This is true and I think this is bad, because now in the grids in the user interface we don't have any means to display an empty string. Instead, it now display the 30/12/1899 string.

We have an issue with Cbre Swiss about this: CFT-2019-95100
Well, that's unexpected. AIS explained me that only the JSON conversion was altered, but what I see in the case concerns just a DataSet display with custom views, I don't believe JSON conversions are involved, right?

And Cbre has 11.1, not 11.2. So, aren't we mixing up...
1,248 questions
1,517 answers
328 users