How to get Dates from Spreadsheet

[HouKaide]

I would like to know how can I cast the date from a Google Spreadsheet. Convert doesn't work and cast doesn't work either.

I've tried all the formats of Converting a string to date from SQL:

I've talked with customer support and they suggested to use a split like this:

return data.map(row => Object.assign(row, {Date: new Date(row.Date.split('-')[2], row.Date.split('-')[1], row.Date.split('-')[0])}))

However, is casting the string to a wrong dates.

I would like to ask if any of you faced something similar and if you have any suggestion.
Thank you in advance.

[Kabirdas]

Hi @HouKaide!

It looks like there are a couple of things going on here. First you pass the index of a month to new Date not the month itself.

From the mdn docs on new Date's second parameter:

monthIndex

Integer value representing the month, beginning with 0 for January to 11 for December. If a value greater than 11 is passed in, then those months will be added to the date; for example, new Date(1990, 12, 1) will return January 1st, 1991

So you might want to do something like this:

return data.map(row => Object.assign(row, {
  Date: new Date(
    row.Date.split('-')[2],
    +row.Date.split('-')[1] - 1,//convert the month string to a number and subtract one to get the correct index
    row.Date.split('-')[0],
  )
}));

Furthermore, when you call new Date in Retool in takes the date as constructed in your timezone and converts it to UTC. So, for instance, when I call new Date(2021, 10, 19) it takes the datetime Nov 17, 2021 00:00:00 PST (which is UTC-8:00) and converts it to Nov 17, 2021 08:00:00. It looks like your timezone might be UTC+1 which means that Nov 17, 2021 00:00:00 would be converted to Nov 16, 2021 23:00:00.

You can offset by also passing in an hour corresponding with your UTC offset to correct for the date, however, I would be hesitant to start manually offsetting your dates since you may want to (or already) rely on the conversion back from UTC to your local timezone without being aware of it.

return data.map(row => Object.assign(row, {
  Date: new Date(
    row.Date.split('-')[2],
    +row.Date.split('-')[1] - 1,
    row.Date.split('-')[0],
    1,//UTC+1 offset - not particularly recommended
  )
}));

Let me know if that helps!