<< Click to Display Table of Contents >> Navigation:  SQL features > Datatypes conversion

Conversion between datatypes can be performed using SQL cast statement, f.e.

 

select cast(A as integer) from data

 

Some functions and operators perform explicit conversion, f.e. sum and avg convert its argument to double, same for +, -, *, /, div, mod operators.

Note that min and max aggregate functions do not perform implicit conversion.

 

Text to number conversion

 

By default text to number conversion expect '.' as decimal separator and no thousands separator. Explicit separators can be passed as second argument to oracle TO_NUMBER function and float() funtction. Example:

 

TO_NUMBER('1,230.15', '.,')
float('3 334,5', ', ')

 

To convert hex string like 0xabc or abc to number, use HextoDec function.

 

Text to date conversion

 

Explicit conversion supports following formats:

YYYY-MM-DD
YYYY-MM-DD HH:NN:SS
DD.MM.YYYY
DD.MM.YY
DD.MM.YYYY HH:NN:SS
DD.MM.YYYY HH:NN
DD.MM.YYYY H:NN
DD-MMM-YYYY HH:NN:SS
DD-MMM-YYYY
DD-MMM-YY

 

For implicit conversion use TO_DATE(String, Format) function.