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?
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))