Casting string column to date in hql

Question

I need to get all records of date superior than 01/01/2015. This query does not work since nt.valeur is of dataType varchar(255). I cannot change the structure of the entity NotifTypeActionChampAddValVO . The database server is on a db2 version 9.7.

Here is the hql:

select nt.valeur from NotifTypeActionChampAddValVO  nt where nt.valide = 1  and nt.typeActionChampAdditionnelValue.champAdditionnel.id = 123  and cast(nt.valeur as date) = '01/01/2015')

I tried by casting cast(nt.valeur as date) but it generates an exception.

here is the exception

DIAVERUM 2015-01-23 13:23:21,859 [http-8081-7] ERROR org.hibernate.util.JDBCExceptionReporter  - DB2 SQL Error: SQLCODE=-461, SQLSTATE=42846, SQLERRMC=SYSIBM.LONG VARCHAR;SYSIBM.DATE, DRIVER=3.63.75
DIAVERUM 2015-01-23 13:23:21,859 [http-8081-7] ERROR org.hibernate.util.JDBCExceptionReporter  - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-461;42846;SYSIBM.LONG VARCHAR|SYSIBM.DATE, DRIVER=3.63.75

How can I cast the column What are the ways this string column can be compared as a date in an hql?

Answer 1

You can use db2 to_date function to cast different string formats to date. For example

date(to_date(column,'DD-MM-YYYY HH:MI:SS'))

So you query could be (depends on the format used on the column nt.valeur)

select nt.valeur from NotifTypeActionChampAddValVO  nt where nt.valide = 1  and nt.typeActionChampAdditionnelValue.champAdditionnel.id = 123  and date(to_date(nt.valeur,'DD-MM-YYYY HH:MI:SS')) = '01/01/2015')

See: TO_DATE scalar function

to_date is actually alias for TIMESTAMP_FORMAT function where format of the format string is documented better.