Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle equivalent to SQL function 'convert'

For e.g in SQL I have:

CONVERT(VARCHAR(10), mydate, 120)
CONVERT(DECIMAL(18,2), cost)

Is there an equivalent for these in Oracle?

like image 791
Derek Avatar asked Nov 14 '10 22:11

Derek


People also ask

What are the conversion functions in Oracle?

Explicit Data Type Conversion SQL Conversion functions are single row functions which are capable of typecasting column value, literal or an expression . TO_CHAR, TO_NUMBER and TO_DATE are the three functions which perform cross modification of data types.

What does convert () do in SQL?

The CONVERT() function converts a value (of any type) into a specified datatype.

What is TO_CHAR function in SQL?

TO_CHAR (datetime) converts a datetime or interval value of DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt .


2 Answers

This in TSQL:

CONVERT(VARCHAR(10), mydate, 120)

...returns a string, so you should probably use TO_CHAR:

TO_CHAR(mydate, 'yyyy-mm-dd hh24:mi:ss')

You'd use TO_DATE if the value is not already an Oracle DATE data type, using the same format mask:

TO_DATE(mydate, 'yyyy-mm-dd hh24:mi:ss')

...or I would anyways, preferring explicit data type conversion when dealing with temporal data types.


This in TSQL:

CONVERT(DECIMAL(18,2),cost)

...needs to use the CAST function:

CAST(cost AS DECIMAL(18,2))
like image 186
OMG Ponies Avatar answered Oct 03 '22 10:10

OMG Ponies


You want to look at the built-in functions for PLSQL.

to_date('15/11/2010''dd/MM/yyyy')

and

to_number('1234.567','999.99')

like image 37
Dave Anderson Avatar answered Oct 03 '22 10:10

Dave Anderson