Forum | Appery.io

Greetings All,

I am having difficulties with a date range query.

I have two date fields, startDate & endDate fields in my db, plus a value (scanned_code) in local storage, that I want to use for a query.

I want to return all records that match the "scanned_code" plus have a startDate earlier or the same as than today and an endDate later or the same as today.

Here is my code.

var scode = localStorage.getItem('scanned_code'); // stored value

var d = new Date();
var today = d.toISOString().replace("T"," ").replace("Z","") ; // today's value

var whereObject = {"$and": [{"userID": scode},{"startDate": {"$lte": today}}, {"e ndDate": {"$gte": today}}]}; // query

return JSON.stringify(whereObject);

Here are two sample records from the db as set using the datepicker.

startDate = 2015-05-01 07:00:00.000
endDate = 2015-05-31 07:00:00.000

The value for "today" returns as = 2015-05-06 22:37:57.508

Thus far the code fails to return the target records. I am also concerned about the recorded datepicker values defaulting to 7 am. I need to ensure all records that fall within the date range are returned, regardless of the time of day.

Any assistance would be greatly appreciated.

Vince

when sending a query to the DB - against dates - you have to drop off (truncated) everything after the seconds in your today value.

Highly suggest you test your query in the tester - and then code against what works...

Bruce

Hi Bruce,

Thanks for the reply. If I truncate the date and leave the hours and minutes, sounds like I might still miss some records that fall on the correct dates but have the wrong hours. I have side stepped this issue elsewhere in my app by recording milliseconds rather than dates. Looks like the best bet is to do the same again, given my limited programming abilities.

I am definitely using the tester to check my queries. Figuring out the correct data to put into the query is the tricky part, especially with dates.

I'm also looking for an offshore developer to assist with some parts of my app beyond my abilities. I have found several potential candidates on the freelance sites, however if you know of a reliable offshore developer, and don't mind sharing, let me know and reply with an email address you can use to forward the info.

Thanks again,

Vince