Casting string to time

Database

Casting string to time

I have a table which has a date field and a text field which along with some other information includes the time information in the 'hh:mi:ss' format.

I want to convert this substring to time and then add it to the date field (cast as timestamp) to get a timestamp column.

However, when I convert the time string to time as cast(time_field as time(0)), the resulting column is the (I) Integer type.

This integer I cannot add to the timestamp field.

I tried doing

select cast(my_date as timestamp(0)) + ((cast(time_field as time(0)) - time '00:00:00') hour to second) -- This does not work due to the time converting as an integer

I also tried

select cast(my_date as timestamp(0)) + interval (cast(time_field as time(0)) second. This obviously doesn't work because it is not a character literal.

I also tried casting the string into an integer, which I then cast as format '99:99:99', which I then cast as string again, which I cast as time(0). Again it doesn't work.

Is there any easy way to convert a string column in the format of hh:mi:ss into a time field?

Tags (1)
1 REPLY
Senior Apprentice

Re: Casting string to time

You submit this query using SQL Assistant?

The ODBC driver modifies a TIME to INT, to get the expectd behaviour change the ODBC "DateTime Format" option from III to AAA or check "Disable Parsing".

But you don't need a TIME, cast to an INTEVAL instead:

cast(my_date as timestamp(0)) + cast(time_field as interval hour to second(0))

Dieter