Vertica Type Conversion Functions and Examples

  • Post author:
  • Post last modified:May 26, 2019
  • Post category:Vertica
  • Reading time:6 mins read

Vertica analytics data warehouse system supports various types of conversion functions. These Vertica type conversion functions are commonly used to changed data type of values stores in database tables.

In SQL, type conversion is sometimes called type casting as well.

These type conversion functions use common calling functions i.e. the first argument is the value to be formatted which will be either expression or table column name, and the second argument is a template that defines the output or input format.

Related articles:

Vertica Type Conversion Functions

Below are some of commonly used Vertica type conversion functions and examples on usage:

Vertica CAST Function

You can do run-time conversions between compatible data types by using the CAST functions. This function is similar to CAST function of the other database. These functions are also called data type coercion operators.

Below is the syntax and examples:

CAST ( expression AS type );

Couple of other way to convert data type at run-time:

expression :: type;

data‑type 'string';

For example;

Below are some common examples on usage of data type coercion operators:

select cast('55655656' as integer);

 ?column?
----------
 55655656
(1 row)

select '55655656'::integer;

 ?column?
----------
 55655656
(1 row)

select integer '55655656';

 ?column?
----------
 55655656
(1 row)

Vertica TO_CHAR Function

Vertica TO_CHAR functions is one of the commonly and widely used function. You can use this function to convert date and time data type to character or text string. i.e. converts the specified value (which can be a date, timestamp, numeric, interval value) to a character string.

The TO_CHAR function comes handy when you are working on miscellaneous data sets.

Below is the syntax and Examples:

TO_CHAR (timestamp_expression | numeric_expression , 'required_format');

For example;

select to_char(date '2019-04-04', 'YYYY Month');

    to_char
----------------
 2019 April
(1 row)

select to_char(timestamp '2019-04-04 20:19:07', 'Day, DD HH:MI:SS am');

          to_char
---------------------------
 Thursday , 04 20:19:07 pm
(1 row)

Vertica TO_NUMBER Function

The Vertica TO_NUMBER function converts a character or text string containing numeric values to a numeric data types.

Below is the syntax and example:

to_number(string, format);

For example;

Select to_number('12.345','99D999');

 to_number
-----------
    12.345
(1 row)

select to_number('-123.456e-01');

 to_number
-----------
  -12.3456
(1 row)

Vertica TO_DATE Function

The Vertica to_date function converts a date represented in a character or text string to a DATE data type.

Below is the syntax and example:

to_date(string, format);

For example;

select to_date ('01 Jan 2000', 'DD Mon YYYY');

  to_date
------------
 2000-01-01
(1 row)

select to_date('2000-1131', 'YYYY-MMDD');

  to_date
------------
 2000-12-01
(1 row)

Related Readings:

Vertica TO_TIMESTAMP Function

The Vertica to_timestamp function converts a datetime represented in a character or text string to a DATETIME data type.

Below is the syntax and example:

to_timestamp(string, format);

For example;

select to_timestamp('2000-1131 01:01:01', 'YYYY-MMDD HH:MI:SS');

    to_timestamp
---------------------
 2000-12-01 01:01:01
(1 row)

Hope this helps 🙂

Tags: ,