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.

Solution 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!

Solution 2

Try this:

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

Or

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

Comments

[fenix]

sql error. But I cannot see error number from this etl console.

[Learning Curve]

I have added one more query to my reply. please give that a try. Second query will return you the hour:minutes only.

[fenix]

'Type (time) Found' for first one. 'Routine (char) can not be unambiguously determined' for second query. Maybe there is a problem in source data. Connection is jdbc.

[fenix]

mistake, source column is not timestamp, it's datetime

[RET]

These suggestions are SQL Server syntax. Unfortunately, putting the sql tag in a question seems to attract SQL Server answers.