CODEWITHSUNDEEP
sqlhsqldb
sethukrs
sethukrs

Reputation: 19

Get String as Date in HSQL

select * 
from employee 
having to_date(date, 'DD/MM/YYYY HH24:MI:SS') = to_date('01/01/2012 10:00:00', 'DD/MM/YYYY HH24:MI:SS')

Column 'date' is a VARCHAR/STRING

as to_date doesnt work in HSQL, how can make the above query work in HSQL ??

Upvotes: 1

Views: 8037

Answers (2)

user3002166
user3002166

Reputation: 730

I know it's an old thread, but it is the top google result so it is probably better to update it than start a new one - especially I have solved my problem. I had a massive problem with this query (hsqldb version 2.3.2) where my date is a string created in java and stored as a varchar in the database.

The to_date call doesn't work without a cast for me and the documentation was a bit vague on how to make the cast correctly (I thought the varchar cast needs to be done specifying the actual length while it doesn't seem to be the case. 

select * from table2 where to_date(cast(timestamp as VARCHAR(254)), 'YYYY-MM-DD') > to_date('2015-09-02', 'YYYY-MM-DD')

notice the cast(xxx as VARCHAR(254)) there!

Upvotes: 0

fredt
fredt

Reputation: 24352

TO_DATE does work with latest versions HSQLDB. You can use version 2.2.9 or future release versions.

But your query is wrong as indicated in comment by a_horse_with_no_name and needs WHERE instead of HAVING.

select * 
from employee 
where to_date(date, 'DD/MM/YYYY HH24:MI:SS') = to_date('01/01/2012 10:00:00', 'DD/MM/YYYY HH24:MI:SS')

In fact you can also simplify the query

select * 
from employee 
where to_date(date, 'DD/MM/YYYY HH24:MI:SS') = timestamp'2012-01-01 10:00:00'

Upvotes: 2

Related Questions