I want to convert a column from VARCHAR2 into a time related data type. For example, from
153312 into 15:33:12
I tried with
to_timestamp(column, 'hh24:mi:ss') as time_column
, but it gives me as a result:
01.11.23 15:33:12,0000000
Any other solution to this?
Oracle doesn't have a time data type. You are converting to a date, so what you are seeing is expected; from the documentation (for date, but applies to timestamps too):
If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.
As you only supplied the time parts, it defaulted to the first day of the current month, 2023-11-01.
Your client or application is then displaying the timestamp value as a string, formatted according to its settings or your NLS_TIMESTAMP_FORMAT session parameter.
As an aside, sort of, it doesn't really make sense to include the colons in your format mask since your original string doesn't have those. Oracle is lenient about that sort of thing by default, but it still looks wrong.
If you only want to see the time part then convert it back to a string, with just those components:
to_char(to_timestamp(your_column, 'hh24miss'), 'hh24:mi:ss') as time_column
Depending on the range of values you have, you can also just manipulate it as a string to insert the separator colons - but you might need to left-pad with zeros first, for example.
And you can convert to an interval data type instead, which might be a better way to store the value than as a string (which is possibly the worst option), but is harder to work with and format than a date or timestamp. It is useful if you plan to add this time value to another datetime though - for example if you're also storing a date as a string in another column (which is another bad practice).