Extract TIME from DATETIME - informix

Question

I have lost few hours looking for one row solution and didn't manage to find it. I have some value (2013/01/03 07:13:26.000) and I want to extract time part to get this: 07:13:26 so I can store it in another database as stage layer in ETL process.

When I try select my_datetime::datetime hour to second, I still get full timestamp.

Thanks.

EDIT: Sorry, source column is datetime, NOT timestamp.

Answer 1

Ok, I managed to do this:

SELECT to_char(extend (my_datetime_column, hour to second),'%H:%M:%S') as my_time FROM my_table

Hope it will help someone!

Answer 2

Try this:

SELECT CAST(datetime_field AS DATETIME HOUR TO SECOND) ...

Or

SELECT to_char(datetime_field, "%H:%M:%S") ...