ESQL convert to ISO8601 yyyy-MM-dd

Question

Given this date format:

"15.03.2016"

How to convert it into ISO8601 yyyy-MM-dd.

Answer 1

You can try something like this

DECLARE MyDate DATE;
SET MyDate = DATE '15.03.2016';
DECLARE newDate CHARACTER;
SET newDate = CAST(CURRENT_DATE AS CHARACTER FORMAT 'dd-MM-yyyy');

Answer 2

The answer from Omar is missing the parsing of the input string. When using the DATE keyword, the string must be in the form 'yyyy-MM-dd'. (See: https://www.ibm.com/docs/en/app-connect/12.0?topic=types-esql-date-data-type) Thats why it can't be used in this case. You should rather use the CAST function to cast the input String to a Date and in a second step cast it to a character in your desired format. The possible formats are listed here: https://www.ibm.com/docs/en/app-connect/12.0?topic=function-formatting-parsing-datetimes-as-strings

So the snippet should look like this:

DECLARE inputDate DATE;
SET inputDate = CAST ('15.03.2016' AS DATE FORMAT 'dd.MM.yyyy');
DECLARE outputDate CHARACTER;
SET outputDate = CAST(inputDate AS CHARACTER FORMAT 'yyyy-MM-dd');