Date/Element range index on MM-DD-YYYY format in MarkLogic

As @rjrudin already mentioned, change the source data to match an xs:date-time format. If the source data cannot be changed, then there are a few alternatives:

1. Brute force:
   • Iterate over documents, dynamically check. Not very efficient and not scalable.
2. Metadata:
   • Use a script to farm the data and add it as a metadata value to the document in the proper format.
   • Then add a field to the metadata
   • Then add a range index
   • Use a range query
3. Triple - a persisted functional index:
   • benefits from the following characteristics of a triple:
     • Linked to document fragment (in all recent versions of MarkLogic)
     • Already stored as a range index
   • Create a TDE template that transforms the date the the appropriate format
   • Store this as the object of a triple
   • Use cts.tripleRangeQuery() for search or op.fromSparql()/op.fromTriples() if using Optic API
4. Same as (3) - but create a view with a column. Then use via Optic, SQL or cts.columnRangeQuery()

You could create a TDE that parses that dateTime value and indexes it as xs:dateTime

Below is an example of how to do that:

'use strict'
const tde = require("/MarkLogic/tde.xqy");
const createdTDE = 
  {
    "template": {
      "context": "/created",
      "vars":[
        {
          "name":"myDateTime",
          "val":"xdmp:parse-dateTime('[M01]-[D01]-[Y0001]T[h01]:[m01]:[s01]', .)"
        }
      ],
      "rows": [
        {
          "schemaName": "test",
          "viewName": "created",
          "columns": [
            {
              "name": "created",
              "scalarType": "dateTime",
              "val": "$myDateTime"
            }
          ]
        }
      ]
    }
  }
tde.templateInsert(
  "/test/createdTDE.json" ,
  createdTDE,
  xdmp.defaultPermissions(),
  ["TDE"]
)

Once you have the TDE in place, you can use it to select where created is greater or less than.

cts.search(
  cts.columnRangeQuery("test", "created", "created", xs.dateTime('2014-12-17T13:40:07'), ">"), 
  "unfiltered"
) 

You need to change the order of the year/month/date - try the following in qconsole to see what's an accepted dateTime:

xs.dateTime("2024-12-17T13:40:07")