All geek questions in one place

How to change date type in Oracle Select?

I usually use PostgreSQL, but now I do it in Oracle.

I need to change the data type of a column in a query (select), in PostgreSQL I usually do it like this:

select 1::varchar from table

Can I do this in Oracle?

Best wishes,

+4
source share
3 answers

convert to varchar

  select to_char(Field) from table

truncate varchar

  select substr(field, 1, 1) from table
+6
source

As Michael Pakhantsov notes, to_char works to convert to string. Similarly, to_date and to_timestamp are standard when converting strings to dates and timestamps, respectively. However, if you find that you need to perform a more exotic conversion (e.g. varchar2 to raw), then cast is your friend:

Number per line:

 select cast(field as varchar2(30)) from table;

String to Raw:

 select cast(field as raw(16)) from table;
+4
source

Like @Allan I like to use cast() since it can go from many types. However, as stated in the official documentation,

CAST does not support LONG, LONG RAW, any LOB data types or Oracle-supplied types

Therefore, it cannot be used to convert to CLOB, for example, Oracle will throw

ORA-00932: inconsistent data types: expected - received CLOB

+1
source

Source: https://habr.com/ru/post/1335223/

More articles:


All Articles