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);
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)}}
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:
$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.$gte and $lte directly, and allow an index to be utilized to improve performance.