CODEWITHSUNDEEP
mongodbmongodb-query
AR7
AR7

Reputation: 376

Converting String Date to Date in MongoDB

I have a collection where the resolve_at field is stored in string format like this:

{"resolved_at": 2020-05-23 05:57:40}

Now I want to convert this to Date type in the format "%Y-%m-%d", using the following query:

db.tickets.aggregate([{"$match":{"botExecResult": "BotSuccess",
    "$and":[{"resolved_at":{"$type":"string","$ne":""}}],
    }},
   { 
   $project: {
      date: {
         $dateFromString: {
            dateString: '$resolved_at',
            format: "%Y-%m-%d"
         
            
         }}}},      
  ])

On executing this, I keep getting this error:

 Error: command failed: {
    "ok" : 0,
    "errmsg" : "Error parsing date string '2020-03-17 23:03:29'; 10: Trailing data ' '",
    "code" : 241,
    "codeName" : "ConversionFailure"
} : aggregate failed 
Details:
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:534:17
assert.commandWorked@src/mongo/shell/assert.js:618:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1062:12
DBCollection.prototype.aggregate@:1:355
@(shell):1:1

is there something wrong in the query structure?

Upvotes: 2

Views: 2753

Answers (3)

AR7
AR7

Reputation: 376

The following query worked for me:

db.tickets.aggregate([{"$match":{"botExecResult": "BotSuccess",
    "$and":[{"resolved_at":{"$type":"string","$ne":""}}],
    }},
   { 
   $project: {
      date: {
         $dateToString:{
             format: '%Y-%m-%d',
             date: {$dateFromString: {dateString: '$resolved_at'}}
      }
  }
   }
   },

   {
       "$match": {"date":{'$lt': "2020-08-05", '$gte': "2020-04-21"}}
   }
  
       
  ])

Upvotes: 5

memodba
memodba

Reputation: 66

You must specify the hour format for resolve the error "Trailling Data".

For you problem, i have a solution with add and second step project after the conversion:

  • My date document string nested in REPORT property :
 StartTime:"2020-06-10 09:04"
  • My aggregate

[
  {
    '$match': {
      'NAME': 'RO150010', 
      '$and': [
        {
          'REPORT.StartTime': {
            '$type': 'string', 
            '$ne': ''
          }
        }
      ]
    }
  }, {
    '$project': {
      'date': {
        '$dateFromString': {
          'dateString': '$REPORT.StartTime', 
          'format': '%Y-%m-%d %H:%M'
        }
      }
    }
  }, {
    '$project': {
      'date2': {
        '$dateToString': {
          'date': '$date', 
          'format': '%Y-%m-%d'
        }
      }
    }
  }
]

Upvotes: 0

turivishal
turivishal

Reputation: 36144

You are using wrong operator, you need to use $dateToString, and to convert string to date you have to use $toDate operator to convert string date to ISOdate because $dateToString operator only allow ISOdate as input,

db.collection.aggregate([
  {
    "$match": {
      "botExecResult": "BotSuccess",
      "$and": [{ "resolved_at": { $type: "string", $ne: "" } }]
    }
  },
  {
    $project: {
      date: {
        $dateToString: {
          date: {
            $toDate: "$resolved_at"
          },
          format: "%Y-%m-%d"
        }
      }
    }
  }
])

Playground

Upvotes: 0

Related Questions