@ConstantFunction(name = "str_to_date", argTypes = {VARCHAR, VARCHAR}, returnType = DATETIME)
    public static ConstantOperator dateParse(ConstantOperator date, ConstantOperator fmtLiteral) {
        DateTimeFormatter builder = DateUtils.unixDatetimeFormatter(fmtLiteral.getVarchar(), false);
        String dateStr = StringUtils.strip(date.getVarchar(), "\r\n\t ");
        if (HAS_TIME_PART.matcher(fmtLiteral.getVarchar()).matches()) {
            LocalDateTime ldt;
            try {
                ldt = LocalDateTime.from(builder.withResolverStyle(ResolverStyle.STRICT).parse(dateStr));
            } catch (DateTimeParseException e) {
                // If parsing fails, it can be re-parsed from the position of the successful prefix string.
                // This way datetime string can use incomplete format
                // eg. str_to_date('2022-10-18 00:00:00','%Y-%m-%d %H:%s');
                ldt = LocalDateTime.from(builder.withResolverStyle(ResolverStyle.STRICT)
                        .parse(dateStr.substring(0, e.getErrorIndex())));
            }
            return ConstantOperator.createDatetimeOrNull(ldt);
        } else {
            LocalDate ld = LocalDate.from(builder.withResolverStyle(ResolverStyle.STRICT).parse(dateStr));
            return ConstantOperator.createDatetimeOrNull(ld.atTime(0, 0, 0));
        }
    }
@Test
    public void dateParse() {
        assertEquals("2013-05-10T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013,05,10"), ConstantOperator.createVarchar("%Y,%m,%d"))
                .getDatetime().toString());
        assertEquals("2013-05-10T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("   2013,05,10   "),
                        ConstantOperator.createVarchar("%Y,%m,%d"))
                .getDatetime().toString());
        assertEquals("2013-05-17T12:35:10", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013-05-17 12:35:10"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i:%s")).getDatetime().toString());

        assertEquals("2013-01-17T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013-1-17"),
                        ConstantOperator.createVarchar("%Y-%m-%d")).getDatetime().toString());

        assertEquals("2013-12-01T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013121"),
                        ConstantOperator.createVarchar("%Y%m%d")).getDatetime().toString());

        assertEquals("2013-05-17T12:35:10.000123", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013-05-17 12:35:10.000123"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i:%s.%f")).getDatetime().toString());

        assertEquals("2013-05-17T12:35:10.000001", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013-05-17 12:35:10.000001"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i:%s.%f")).getDatetime().toString());

        assertEquals("2013-05-17T12:35:10", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013-05-17 12:35:10.00000"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i:%s.%f")).getDatetime().toString());

        assertEquals("2013-05-17T00:35:10", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013-05-17 00:35:10"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i:%s")).getDatetime().toString());
        assertEquals("2013-05-17T23:35:10", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("abc 2013-05-17 fff 23:35:10 xyz"),
                        ConstantOperator.createVarchar("abc %Y-%m-%d fff %H:%i:%s xyz")).getDatetime().toString());
        assertEquals("2019-05-09T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2019,129"), ConstantOperator.createVarchar("%Y,%j"))
                .getDatetime().toString());
        assertEquals("2019-05-09T12:10:45", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("12:10:45-20190509"),
                        ConstantOperator.createVarchar("%T-%Y%m%d")).getDatetime().toString());
        assertEquals("2019-05-09T09:10:45", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("20190509-9:10:45"),
                        ConstantOperator.createVarchar("%Y%m%d-%k:%i:%S")).getDatetime().toString());
        assertEquals("2020-02-21 00:00:00",
                ScalarOperatorFunctions.dateParse(ConstantOperator.createVarchar("2020-02-21"),
                        ConstantOperator.createVarchar("%Y-%m-%d")).toString());
        assertEquals("2020-02-21 00:00:00",
                ScalarOperatorFunctions.dateParse(ConstantOperator.createVarchar("20-02-21"),
                        ConstantOperator.createVarchar("%y-%m-%d")).toString());
        assertEquals("1998-02-21 00:00:00",
                ScalarOperatorFunctions.dateParse(ConstantOperator.createVarchar("98-02-21"),
                        ConstantOperator.createVarchar("%y-%m-%d")).toString());

        Assert.assertThrows(DateTimeException.class, () -> ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("201905"),
                        ConstantOperator.createVarchar("%Y%m")).getDatetime());

        Assert.assertThrows(DateTimeException.class, () -> ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("20190507"),
                        ConstantOperator.createVarchar("%Y%m")).getDatetime());

        Assert.assertThrows(DateTimeParseException.class, () -> ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2019-02-29"),
                        ConstantOperator.createVarchar("%Y-%m-%d")).getDatetime());

        Assert.assertThrows(DateTimeParseException.class, () -> ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2019-02-29 11:12:13"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i:%s")).getDatetime());

        Assert.assertThrows(IllegalArgumentException.class,
                () -> ScalarOperatorFunctions.dateParse(ConstantOperator.createVarchar("2020-2-21"),
                        ConstantOperator.createVarchar("%w")).getVarchar());

        Assert.assertThrows(IllegalArgumentException.class,
                () -> ScalarOperatorFunctions.dateParse(ConstantOperator.createVarchar("2020-02-21"),
                        ConstantOperator.createVarchar("%w")).getVarchar());
        assertEquals("2013-01-17T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("\t 2013-1-17"),
                        ConstantOperator.createVarchar("%Y-%m-%d")).getDate().toString());
        assertEquals("2013-01-17T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("\n  2013-1-17"),
                        ConstantOperator.createVarchar("%Y-%m-%d")).getDate().toString());
        assertEquals("2013-01-17T00:00", ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("\r  2013-1-17"),
                        ConstantOperator.createVarchar("%Y-%m-%d")).getDate().toString());

        Assert.assertThrows(DateTimeParseException.class,
                () -> ScalarOperatorFunctions.dateParse(ConstantOperator.createVarchar("\f 2020-02-21"),
                        ConstantOperator.createVarchar("%Y-%m-%d")).getVarchar());

        Assert.assertThrows("Unable to obtain LocalDateTime", DateTimeException.class, () -> ScalarOperatorFunctions
                .dateParse(ConstantOperator.createVarchar("2013-05-17 12:35:10"),
                        ConstantOperator.createVarchar("%Y-%m-%d %h:%i:%s")).getDatetime());

        assertEquals("2022-10-18T01:02:03", ScalarOperatorFunctions.dateParse(
                        ConstantOperator.createVarchar("2022-10-18 01:02:03"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i:%s")).
                getDatetime().toString());

        assertEquals("2022-10-18T01:02", ScalarOperatorFunctions.dateParse(
                        ConstantOperator.createVarchar("2022-10-18 01:02:03"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%i")).
                getDatetime().toString());

        Assert.assertThrows("Unable to obtain LocalDateTime", DateTimeException.class,
                () -> ScalarOperatorFunctions.dateParse(
                        ConstantOperator.createVarchar("2022-10-18 01:02:03"),
                        ConstantOperator.createVarchar("%Y-%m-%d %H:%s")).getDatetime());
    }