-1

I'm getting data (including date fields) from a Google Sheets document and importing into MongoDB (via Mongo Realm, although I suspect Node, Lambda etc would be the same).

The date field in sheets is data validated as a date (and date is chosen via date picker) but when I console.log the typeof the variable I get in shows as a string with the value like "Thu Aug 20 00:00:00 GMT+01:00 2020".

If I run it through new Date() logging reports it is NaN-aN-aN and when it hits Mongo it shows as 0001-01-01T00:00:00.000+00:00.

Does anyone know how to to get the date in the Google Sheet as a Date in Mongo? The JSON schema on the collection for that field is

 "purchase_date": {
      "bsonType": "date"
    },

I'm also trying to insert a year 2020 into Mongo as a Date type if anyone knows how to specify that?

Stuart Brown
  • 977
  • 2
  • 22
  • 47
  • Can you use moment.js for this ? – Carlos1232 Aug 27 '20 at 22:48
  • 1
    `console.log(new Date('Thu Aug 20 00:00:00 GMT+01:00 2020').toString());`. Looks valid to me. – StackSlave Aug 27 '20 at 22:50
  • Possibly a duplicate of [*Store date in MongoDB without considering the timezone*](https://stackoverflow.com/questions/27483334/store-date-in-mongodb-without-considering-the-timezone). Are you getting dates from Google Sheets as Date objects or strings? – RobG Aug 28 '20 at 02:33
  • sorry @StackSlave that shows 'Invalid Date' for me – Stuart Brown Aug 28 '20 at 07:45

2 Answers2

0

You can format as you want with these two libraries.

let date = 'Thu Aug 20 00:00:00 GMT+01:00 2020'
let momentDate = moment(date).format('MM-DD-YYYY')
console.log(momentDate)

let dayJsDate = dayjs(date).format('MM-DD-YYYY')
console.log(dayJsDate)
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.27.0/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/dayjs/1.8.34/dayjs.min.js"></script>
Carlos1232
  • 815
  • 6
  • 15
  • The question is "*Does anyone know how to to get the date in the Google Sheet as a Date in Mongo?*" I don't see any attempt to answer that. – RobG Aug 28 '20 at 02:28
  • Passing unsupported formats to moment.js without parse tokens means it's parsed by the built–in parser, so no different to `new Date(string)`. – RobG Aug 28 '20 at 02:45
-1

let date = 'Thu Aug 20 00:00:00 GMT+01:00 2020';
date = new Date(date) //date object
console.log(date)
console.log(typeof(date))
console.log(date.toLocaleDateString());
Muhammad Saquib Shaikh
  • 296
  • 2
  • 13