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?

Solution 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.

Solution 2

You can do this without trying to cast string to date. Try this

select nt.valeur from NotifTypeActionChampAddValVO  nt where nt.valide = 1  and nt.typeActionChampAdditionnelValue.champAdditionnel.id = 123  and concat(substring(nt.valeur, 7, 4), substring(nt.valeur, 4, 2), substring(nt.valeur, 1, 2)) > '20150101'

This is under assumption that 01/01/2015 from your example is dd/MM/yyyy (but can be changed easily), and that you can adapt the parameter format to yyyyMMdd.

Solution 3

Try this one:-

from NotifTypeActionChampAddValVO  nt where nt.valide = 1  and nt.typeActionChampAdditionnelValue.champAdditionnel.id = 123  and nt.valeur ='2015-01-01'

Comments

[Manish Basdeo]

I get the same exception as above

[ikettu]

String format of date might depend on locale setting. Please try 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')) = date(to_date('01-01-2015 00:00:00','DD-MM-YYYY HH:MI:SS'))). But please edit the format string according the format of your cells.

[Anil Uttani]

This will not compare dates ,Rather It will compare both the Strings.