Wildcard search on Date fields

I use HSQLDB2.0 and JPA2.0 for my current project and have few date columns in DB.

I would like to run wildcard queries on the date columns. How could I do that?

Ex : If my DB contains two rows with date values as : 10-01-2011 and 15-02-2011

and my search criteria 开发者_JAVA技巧will be "%10-01%", then result should be 10-01-2011.

Else if search criteria is "%2011%" then both rows need to be fetched with the select query.

Thanks in advance,

Satya

You can define an autogenerated column of type VARCHAR containing a copy of the date. You can then perform queries with both LIKE predicates and REGEXP_MATCHES() function. An example of the column definition is below:

DATEGEN VARCHAR(10) GENERATED ALWAYS AS (CAST(DATECOL AS VARCHAR(10))

Note the string representation of DATE is in the form '2011-02-26' and your query strings should follow this pattern.

This can be achieved in this format :

select date_birth from member where to_char(date_birth,'MM-yyyy') like '%02-2011%'

select date_birth from member where to_char(date_birth,'MM-dd') like '%02-15%'

select date_birth from member where to_char(date_birth,'dd-yyyy') like '%30-2011%'

Regards,

Satya