SELECT code FROM usertype
WHERE CAST(CONVERT(varchar, CREATEDATE, 120) as date) = CAST(CONVERT(varchar, '2024-07-08', 120) as date);

I want to write expression for the condition ‘CAST(CONVERT(varchar, CREATEDATE, 120) as date) = CAST(CONVERT(varchar, '2024-07-08', 120) as date)‘ found in the query above. I tried different solutions but I couldn’t get it to work properly.

 private static Predicate getDatePredicate(CriteriaBuilder criteriaBuilder, Path path, Object value,
                                          Predicate mainPredicate, SqlOperator sqlOperator) {

    Expression<String> formattedPath = criteriaBuilder.function(
            "CONVERT", String.class, criteriaBuilder.literal("varchar"), path, criteriaBuilder.literal(120));
    Expression<String> formattedValue = criteriaBuilder.function(
            "CONVERT", String.class, criteriaBuilder.literal("varchar"), criteriaBuilder.literal(value), criteriaBuilder.literal(120));

    Expression<Date> truncatedPath = criteriaBuilder.function("CAST", Date.class,
            criteriaBuilder.concat(formattedPath, criteriaBuilder.literal(" AS date")));
    Expression<Date> truncatedValue = criteriaBuilder.function("CAST", Date.class,
            criteriaBuilder.concat(formattedValue, criteriaBuilder.literal(" AS date")));

    return switch (sqlOperator) {
        case EQUAL -> criteriaBuilder.and(mainPredicate,
                criteriaBuilder.equal(truncatedPath, truncatedValue));
        default -> mainPredicate;
    };
}

When I try this way. I get the error

org.hibernate.query.sqm.produce.function.FunctionArgumentException: Function cast() has 2 parameters, but 1 arguments given.

Note: The following I wrote for Oracle works without any problems.

private static Predicate getDatePredicate(CriteriaBuilder criteriaBuilder, Path path, Object value,
                                              Predicate mainPredicate, SqlOperator sqlOperator) {
        Expression<String> formattedPath = criteriaBuilder.function("TO_CHAR", String.class, path, criteriaBuilder.literal("YYYY.MM.DD"));
        Expression<String> formattedValue = criteriaBuilder.function("TO_CHAR", String.class, criteriaBuilder.literal(value), criteriaBuilder.literal("YYYY.MM.DD"));

        Expression<Date> truncatedPath = criteriaBuilder.function("TO_DATE", Date.class, formattedPath, criteriaBuilder.literal("YYYY.MM.DD"));
        Expression<Date> truncatedValue = criteriaBuilder.function("TO_DATE", Date.class, formattedValue, criteriaBuilder.literal("YYYY.MM.DD"));

        return switch (sqlOperator) {
            case EQUAL -> criteriaBuilder.and(mainPredicate,
                    criteriaBuilder.equal(truncatedPath, truncatedValue));
            default -> mainPredicate;
        };
    }