0

I'm trying to format the date in Google App Script and then store it into the Google Sheet. Everything is working fine but the problem is source date has no specific format, dates may come in different formats like DD-MM-YYYY, MM-DD-YYYY, YYYY-MM-DD or date can also come with time and then I've to convert it into YYYY-MM-DD format and then save in google sheet. I've tried to convert to covert using the below codes:

var date = new Date("01-15-2022").toISOString().split('T')[0] // MM/dd/yyyy
Logger.log(date);

this code works only if the source date has in a format like MM/dd/yyyy and yyyy/MM/dd only if the date is in a format like dd/MM/yyyy then codes does not work.

I want a method that converts all the date formats in a single format like yyyy/MM/dd

  • 2
    Consider using [`Intl.DateTimeFormat()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/DateTimeFormat/DateTimeFormat) instead? – evolutionxbox Jan 18 '22 at 08:36
  • @evolutionxbox, how to use YYYY-MM-DD format inside this function? –  Jan 18 '22 at 08:52
  • `new Date('2022-01-15')` works, but `new Date('01-15-2022')` is an invalid date since Date cannot parse `MM-DD-YYYY` – evolutionxbox Jan 18 '22 at 08:54
  • @evolutionxbox, that's why here I'm asking to convert any format into `yyyy-MM-dd` format. –  Jan 18 '22 at 08:57
  • 1
    Does this answer your question? [Format date to MM/dd/yyyy in JavaScript](https://stackoverflow.com/questions/11591854/format-date-to-mm-dd-yyyy-in-javascript) - use `Intl` for the output, but it should help you parse any date. – evolutionxbox Jan 18 '22 at 08:58
  • 1
    "*source date has no specific format, dates may come in different formats*" then you're stuffed. You can only reliably parse a timestamp if you know the format, or it's unambiguous (e.g. use the month name). Otherwise, you're asking for an unachievable result. – RobG Jan 18 '22 at 08:59
  • This https://webapps.stackexchange.com/a/100644 – Kos Jan 18 '22 at 10:40

2 Answers2

3

I got a little bad news for you. There is no way to distinguish algorithmically between 01-02-2022 (Feb 1, 2022) and 01-02-2022 (Jan 2, 2022). So technically this source data is the infamous 'garbage in'.

Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • Is any way through which we can know the difference between day and month. If the day is lesser than 12 (month)? –  Jan 19 '22 at 07:35
  • Of course you can parse some dates since we have 12 month only. Do you need it? Do you need help to figure out this partial solution? It’s a set of quite trivial tricks. (But there will be troubles again as soon as you come across the dates with YY like ‘21-10-22’) – Yuri Khristich Jan 19 '22 at 08:49
0

Somehow I managed to find an alternative option to achieve this using Moment Js Library below is the solution:

function myFunction() {
  let formats = ["DD/MM/YYYY", "MM/DD/YYYY", "YYYY/MM/DD", "DD/MM/YYYY hh:mm:ss", "DD/MM/YYYY hh:mm:ss A", "MM/DD/YYYY hh:mm:ss A", "YYYY/MM/DD hh:mm:ss A"]
  Logger.log(convertDate("5/1/2022 12:00:00 PM", formats));
}


function convertDate(date, formats){
  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.9.0/moment.min.js').getContentText());
  let dateMomentObject = moment(date, formats);
  let dateObject = dateMomentObject.toDate();
  let dateStr = moment(dateObject).format('YYYY-MM-DD');
  return dateStr;
}