sql - 如何在JPQL中将日期时间转换为日期？(How to convert datetime to date in JPQL?)

Question

What's the problem with this code?

(此代码有什么问题？)

@Query(value = "Select " +
    "date(ivd.trnDatetime) as date, " +
    "ivd.binNo as bin, " +
    "ivd.snNo as sn, " +
    "count(ivd.invoiceNo) as totInvoice, " +
    "sum(ivd.totSaleAmt) as totSaleAmt " +
    "from InvoiceData ivd where ivd.status = 1 group by date(ivd.trnDatetime), ivd.binNo, ivd.snNo")
List<Object[]> getDailyTransactionReport();

It throws

(它抛出)

    java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 +-[METHOD_CALL] MethodNode: '('
 |  +-[METHOD_NAME] IdentNode: 'date' {originalText=date}
 |  -[EXPR_LIST] SqlNode: 'exprList'
 |     -[DOT] DotNode: 'invoicedat0_.transaction_at' {propertyName=trnDatetime,dereferenceType=PRIMITIVE,getPropertyPath=trnDatetime,path=ivd.trnDatetime,tableAlias=invoicedat0_,className=com.efdms.transactionmonitoring.domain.InvoiceData,classAlias=ivd}
 |        +-[ALIAS_REF] IdentNode: 'invoicedat0_.id' {alias=ivd, className=com.efdms.transactionmonitoring.domain.InvoiceData, tableAlias=invoicedat0_}
 |        -[IDENT] IdentNode: 'trnDatetime' {originalText=trnDatetime}
 [Select date(ivd.trnDatetime) as date, ivd.binNo as bin, ivd.snNo as sn, count(ivd.invoiceNo) as totInvoice, sum(ivd.totSaleAmt) as totSaleAmt from com.efdms.transactionmonitoring.domain.InvoiceData ivd where ivd.status = 1 group by date(ivd.trnDatetime), ivd.binNo, ivd.snNo]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:351)
    at com.sun.proxy.$Proxy190.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87)
    ... 62 common frames omitted

The property for trnDatetime in entity

(实体中trnDatetime的属性)

@NotNull
@Column(name = "transaction_at", nullable = false)
private Instant trnDatetime;

and equivalent column in SqlServer is datetime

(并且SqlServer中的等效列是datetime)

  ask by shovon translate from so

Answer 1

The error message tells you that there is no value for transaction_at .

(错误消息告诉您没有transaction_at值。)

This means that some records in your table have a null value for transaction_at or that it is not selected by the query for some reason.

(这意味着表中的某些记录的transaction_at值为null ，或者由于某种原因查询未选择该记录。)

You will need to make sure that you select it and in case it is null you will either have a default value for it, or you filter out records which have null as a value for that particular field.

(您需要确保select它，如果它为null ，则将为其select默认值，或者过滤掉该字段为null记录。)

Also, if a value for transaction_at is not necessarily required, then you can remove nullable = false from the @Column .

(另外，如果不一定需要transaction_at的值，则可以从@Column删除nullable = false 。)

EDIT

(编辑)

The solution, as pointed out in the comment section was CAST(ivd.trnDatetime AS date)

(如评论部分所指出的，解决方案是CAST(ivd.trnDatetime AS date))