Query to change date format

SQL++
1

Hi I found out I have been saving dates in format “'d M Y” which not allows me to query documents using date between and date.

Is there query to change all documents dates from its current date format to ISO 8610 date format?

Result for each doc would be if one has date for example “09 Sep 2019” will become “2019-09-09T00:00:00+0100”.

Please help because I have plenty of documents with wrong date.

2

The following query converts the date into desired format.

SELECT CONCAT(SUBSTR(date,-4), "-",(FIRST v[1] FOR v IN [["Jan", "01"], ["Feb","02"], ["Mar","03"], ["Apr", "04"], ["May","05"], ["Jun","06"], ["Jul","07"], ["Aug", "08"], ["Sep","09"] ,["Oct","10"], ["Nov","11"],["Dec","12"]] WHEN v[0] = SUBSTR(date,3,3) END), "-", SUBSTR(date,0,2), "T00:00:00+0100")
 LET date = "09 Sep 2019";

OR

SELECT CONCAT(SUBSTR(date,-4), "-",{"Jan":"01", "Feb":"02", "Mar":"03", "Apr":"04","May":"05","Jun":"06","Jul":"07","Aug":"08","Sep":"09","Oct":"10","Nov":"11","Dec":"12"}.[SUBSTR(date,3,3)], "-", SUBSTR(date,0,2), "T00:00:00+0100")
 LET date = "09 Sep 2019";

You can use similar update to update the field. STR_TO_MILLIS(d.date) converts date into millis. If the date is already ISO-8601 format it will gives integer otherwise it returns NULL. This allows the not to update the row if already in ISO 8601 format

UPDATE default AS d
SET d.date = CONCAT(SUBSTR(d.date,-4), "-",(FIRST v[1] FOR v IN [["Jan", "01"], ["Feb","02"], ["Mar","03"], ["Apr", "04"], ["May","05"], ["Jun","06"], ["Jul","07"], ["Aug", "08"], ["Sep","09"] ,["Oct","10"], ["Nov","11"],["Dec","12"]] WHEN v[0] = SUBSTR(d.date,3,3) END), "-", SUBSTR(d.date,0,2), "T00:00:00+0100")
WHERE STR_TO_MILLIS(d.date) IS NULL;

OR

UPDATE default AS d
SET d.date = CONCAT(SUBSTR(d.date,-4), "-",{"Jan":"01", "Feb":"02", "Mar":"03", "Apr":"04","May":"05","Jun":"06","Jul":"07","Aug":"08","Sep":"09","Oct":"10","Nov":"11","Dec":"12"}.[SUBSTR(date,3,3)], "-", SUBSTR(d.date,0,2), "T00:00:00+0100")
WHERE STR_TO_MILLIS(d.date) IS NULL;