CODEWITHSUNDEEP
oracle-databasetypes
André
André

Reputation: 25584

How to change the date type in a Oracle Select?

I'm usualy use PostgreSQL, but I'm currently doing it in Oracle.

I need to chage the data type of a column in a query(select), in PostgreSQL I usualy do it in this way:

select 1::varchar from table

Hoe can I do this in Oracle?

Best Regards,

Upvotes: 4

Views: 16267

Answers (3)

mach128x
mach128x

Reputation: 339

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

CAST does not support LONG, LONG RAW, any of the LOB datatypes, or the Oracle-supplied types

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

ORA-00932: inconsistent datatypes: expected - got CLOB

Upvotes: 1

Allan
Allan

Reputation: 17429

As @Michael Pakhantsov points out, to_char works for converting to string. Likewise, to_date and to_timestamp are the standard when converting strings to dates and timestamps respectively. However, if you find that you need to perform a more exotic conversion (varchar2 to raw, for instance), then cast is your friend:

Number to string:

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

String to Raw:

select cast(field as raw(16)) from table;

Upvotes: 4

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

convert to varchar

 select to_char(Field) from table

truncate varchar

 select substr(field, 1, 1) from table

Upvotes: 8

Related Questions