How to format date fields in Apache Calcite?

Question

I would like to create a RexNode to convert date fields in desired format. Below is the SQL equivalent

SELECT CONVERT(varchar(12), "DATE_FIELD", 101) - 06/29/2009

I have been struggling past two days to find it in the calcite API. Please help me with this.

score 0 · Answer 1 · answered Mar 11 '20 at 07:20

I have resolved the issue with a custom defined Sql operator.

public static SqlOperator getConvertOperator () {
    return new SqlFunction(CONVERT_FUNCTION, SqlKind.OTHER_FUNCTION,
        ReturnTypes.BOOLEAN, InferTypes.FIRST_KNOWN,
        OperandTypes.ANY, SqlFunctionCategory.SYSTEM);
}

RexNode node = builder.call(getConvertOperator(), builder.literal(SqlTypeName.VARCHAR), builder.field(COLUMN_NAME), builder.literal(FORMAT));

That just did the trick.

How to format date fields in Apache Calcite?

1 Answers1