How to query date saved as text in bad date format in mongoDB

I am very new to mongodb I have a database with sale_date and the value is saved as text and the format is "dd:mm:yyyy". Now I want to query based on the date. Like I want to query the last month's entry. I also have field sale_time and also saved as text and the format is "hh:mm" and I want to query the last hour's entry.

**I want to query from java and also from the mongo console.

One row of my collection:

    {  
       "_id":112350,
       "sale_date":"21.07.2011",
       "sale_time":"18:50",
       "store_id":"OK3889-45",
       "region_code":45,
       "product_id":"QKDGLHX5061",
       "product_catagorie":53,
       "no_of_product":1,
       "price":1211.37,
       "total_price":1211.37
    }

I have million of entries. Now I want to find the entries for the month of July 2011 or hour from 18:00 to 19:00 in 21.07.2013.

Solution

You can query with a regex matching your results. You said format dd:mm:yyyy but the example looks like dd.mm.yyyy so I used that in examples

For example:

db.sales.find({sale_date: /..\.07\.2011/})

This will be ineficient since it can't use an index, but it will get the job done.

It would be better, if you stick with dates as strings to reverse the order to yyyy:mm:dd then you could use a anchored regex, which will hit an index like:

db.sales.find({sale_date: /2011\.07/})

For the hour query:

db.sales.find({sale_date: "21.07.2013", sale_time: {$gte: "18:00", $lt: "19:00"}})