Informix SQL Compare DateTime value on Where Statement

datetime, informix, sql

Solution

Use the EXTEND() function to extract the time part:

select *
from mytable
where extend(entrytimestamp, hour to second) > '09:00:00'

Problem

I have a datetime field called `entrytimestamp`, with content of the field is for example: `2014-01-07 16:20:00`. I would like to query all the data that has `entrytimestamp` after `09:00:00` o'clock, regardless what date it was. I have a prototype query: ``` select * from trading where to_char(entrytimestamp, "%H%M%S") >= "090000" ``` But I think it is logically a mistake, because it will compare the text string, not the sequence value. What is the right way to do it?

Original source