Can $regex and $gte/$lte for strings in mongodb be used together?-CodePudding

I've transaction date in mongodb database in the format "7/1/2021 12:30:48 PM" (string in M/D/YYYY). From React side, I'm entering two dates : From date and To Date which are both strings in the format YYYY-MM-DD. I'm using the below code, to find transactions that come between From and To dates. I'm getting a cast error but all the dates are in string format. It works perfectly with only one date without $gte and $lte.

ERROR Message: CastError: Cast to string failed for value "{ '$regex': '7/1/2021', '$options': '$i' }" (type Object) at path "Trandate" for model "transaction"

const chooseWinner = await Transaction.find(
    {
      ContCode: CountryCode,
      CurrCode: CurrCode,
      CorrOrgCode: CorrorgCode,
      ServCode: ServCode,
      BranchCode: BranchCode,
      Trandate: {$gte: {$regex : SDate, $options: "$i"}},
      Trandate: {$lte: {$regex : EDate, $options: "$i"}},
    },
    "CustomerCode ReferenceNo Trandate "
  );

Also I've changed the from and to dates to the below format:

var SDate = new Date(FromDate).toLocaleDateString("hi-IN",{month:'numeric'}) "/" 
                  new Date(FromDate).toLocaleDateString("hi-IN",{day:'numeric'}) "/" 
                  new Date(FromDate).toLocaleDateString("hi-IN",{year:'numeric'});
  console.log("frmdate: ",SDate);
  

  var EDate = new Date(ToDate).toLocaleDateString("hi-IN",{month:'numeric'}) "/" 
                  new Date(ToDate).toLocaleDateString("hi-IN",{day:'numeric'}) "/" 
                  new Date(ToDate).toLocaleDateString("hi-IN",{year:'numeric'});
  console.log("enddate: ",EDate);

CodePudding user response：

Don't Know about the regex and $gte/$lte together but it seems like issue is with date format. Why don't use use slandered

{Trandate:{$gte:ISODate(FromDate),$lt:ISODate(ToDate)}}

CodePudding user response：

To answer the directly asked question, no, the $regex query operator cannot be nested inside an inequality operator like that.

The $regex operator returns true or false indicate whether or not the string matched, so even if it were permitted, that nested comparison would effectively be "less than or equal to (boolean)". Since mongodb query operators are type sensitive, that comparison will always be false.

To answer the implied question How can I query these dates by a specified range?:

Dates stored in the string format you show cannot be meaningfully compared with inequality operations.

For example, here are a few dates in sorted order:

    "6/1/2022 1:30 PM",
    "7/1/2021 12:30:48 PM",
    "7/1/2021 9:30:48 AM",
    "7/12/2021 12:30:48 PM",
    "7/2/2021 12:30:48 PM",
    "8/1/2001 8:55:00 AM"

If you were to query for {$lte: "8/1/2001 8:55:00 AM"} all 6 of those dates would match.

In order to query a range of dates, you will need to either:

Query with aggregation
use a pipeline that reads every document in the collection, uses $dateFromString aggregation operator to convert the strings to dates, then match on the converted field. This is not scalable, and will not perform well under any non-trivial load.
Modify the schema
Change the application to convert the string dates to a Date object before storing in the database. All existing documents will also need to be modified. This will permit you to query against the field with $gte and $lte directly, and allow an index to be utilized to improve performance.