12

enter image description here

I am uploading data from a csv file to MongoDB. It is taking OrderDate as string data type due to which facing problems while creating reports using a BI tool. I have about 10000 records in my collection.

Could anyone help me how can I change the data Type of OrderDate to Date with a single query?

shA.t
  • 16,580
  • 5
  • 54
  • 111
divya
  • 187
  • 2
  • 2
  • 7
  • 1
    Possible duplicate of [Converting string to date in mongodb](http://stackoverflow.com/questions/10942931/converting-string-to-date-in-mongodb) – Musa Haidari Oct 31 '15 at 09:39

2 Answers2

25

I don't think that you can change field's type with single query. The easiest way is to convert data strings into Date format using ISODate function during the insertion. But, if you want to process the data you already inserted, you can do it with the following code using mongodb console:

db.collection.find().forEach(function(element){
  element.OrderDate = ISODate(element.OrderDate);
  db.collection.save(element);
})

This code will process each element in your collection collection and change the type of Orderdate field from String to Date.

Leonid Beschastny
  • 50,364
  • 10
  • 118
  • 122
  • ThankYou that was really helpfull. But here I see its creating a new feild. Is there anyway to change the existing one without creating a new field. – divya Mar 19 '13 at 06:56
  • It's rewriting the old field with the new value, not creating the new one. – Leonid Beschastny Mar 19 '13 at 07:07
  • 1
    @divya MongoDB is case-sensitive. You took the value from `OrderDate` field and placed the new one into **another** field `Orderdate`, instead of using the same one. I edited both you question and my answer with the correct field name. – Leonid Beschastny Mar 19 '13 at 07:42
  • How do I convert it the opposite way from a date type to a string... having a hard time. – Lion789 Jun 22 '16 at 15:51
  • @Lion789 have you tried [.toISOString()](https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Date/toISOString)? – Leonid Beschastny Jun 22 '16 at 22:40
  • As an FYI to others, `Collection.save()` is deprecated. The error notes: "Use insertOne, insertMany, updateOne or updateMany." – Demitri Apr 01 '21 at 19:52
1
db.messages.find().forEach(function(doc) {
  doc.headers.datestamp = new Date(Date.parse(doc.headers.Date.toString())); 
  db.messages.save(doc);
  })

This worked well to convert this sort of text: Tue, 14 Nov 2007 03:22:00 -0800 (PST)

The text is from an email archive known as the Enron Corpus.

Paul Nord
  • 41
  • 2
  • As an FYI to others, `Collection.save()` is deprecated. The error notes: "Use insertOne, insertMany, updateOne or updateMany." – Demitri Apr 01 '21 at 19:52