Sql Server下如何用CriteriaBuilder实现Convert(DATE, expr)日期转换？

针对SQL Server 2019环境下，JPA Specification动态查询中实现convert(DATE, date_col)日期截断的需求，有三种可行实现方式：

方案1：直接使用CriteriaBuilder.function（适配Hibernate默认SQL Server方言） #

Hibernate的SQL Server方言对原生convert函数有特殊适配，第一个参数传入字符串类型的类型名不会被额外添加单引号，可直接使用：

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    // 获取日期字段
    Expression<Date> dateCol = root.get("date_col");
    // 调用convert函数，指定返回类型为LocalDate，第一个参数传类型名字面量
    Expression<LocalDate> convertedDate = criteriaBuilder.function(
        "convert",
        LocalDate.class,
        criteriaBuilder.literal("DATE"),
        dateCol
    );
    // 构造比较条件，入参为LocalDate类型的对比值
    return criteriaBuilder.greaterThan(convertedDate, 你的对比日期参数);
}

方案2：自定义方言注册函数（兼容性最优，无语法风险） #

如果方案1因为Hibernate版本差异出现DATE被加单引号的语法错误，可通过自定义方言注册专用的日期截断函数，彻底规避类型参数传递问题：

1. 自定义方言类继承官方SQL Server方言，注册自定义函数

import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.type.StandardBasicTypes;

public class CustomSqlServerDialect extends SQLServer2012Dialect {
    public CustomSqlServerDialect() {
        super();
        // 注册日期截断函数，模板里直接写死DATE类型，无需动态传参
        registerFunction("trunc_date", 
            new SQLFunctionTemplate(StandardBasicTypes.LOCAL_DATE, "convert(DATE, ?1)"));
    }
}

2. 配置JPA使用自定义方言，在application.yml中添加配置：

spring:
  jpa:
    properties:
      hibernate:
        dialect: 你的包路径.CustomSqlServerDialect

3. 在Specification中直接调用注册好的函数：

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    Expression<LocalDate> truncatedDate = criteriaBuilder.function(
        "trunc_date",
        LocalDate.class,
        root.get("date_col")
    );
    return criteriaBuilder.greaterThan(truncatedDate, 你的对比日期参数);
}

方案3：规避字段函数转换（性能最优，推荐优先使用） #

如果业务允许，可调整查询逻辑，不对字段做函数转换，而是将对比参数扩展为时间范围，不仅无需处理类型参数问题，还能保留date_col字段的索引可用性：

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    // 例如要查询convert(DATE, date_col) > '2024-05-20'，等价于date_col > '2024-05-20 23:59:59.999'
    LocalDate compareDate = 你的对比日期参数;
    // 构造参数的最大时间边界
    LocalDateTime maxTimeBoundary = compareDate.atTime(23, 59, 59, 999_999_999);
    return criteriaBuilder.greaterThan(root.get("date_col"), maxTimeBoundary);
}

内容的提问来源于stack exchange，提问作者Ayush