why using LIKE with TIMESTAMPS do not work in DB2

Question

i have problem using LIKE structure in DB2: for example:

select * from TEST where TIME LIKE '2012-03-04-%'

FYI. - TIME is TIMESTAMP data type.

why using LIKE with TIMESTAMPS do not work?

Additional info: i want to extract data from one single day provided by user in select statement.

Answer 1

You can use like this where time between to_date('2016-06-17 00:00:00','yyyy-mm-dd HH24:MI:SS') and to_date('2016-06-18 00:00:00','yyyy-mm-dd HH24:MI:SS')

Answer 2

LIKE is for string (char, varchar) datatypes. Use WHERE time BETWEEN '2012-03-04' AND '2012-03-04 23:59:59.998'

Answer 3

Just expanding on @mortb's answer, I'd either use BETWEEN or

WHERE time >= '2012-03-04' AND time < '2012-03-05'

The advantage of using BETWEEN or a comparison that using casts and LIKE will mean that if there is an index on time it wouldn't be able to be used due to the casting.