Netezza supports various conversion functions. Below are some of commonly used Netezza conversion functions.
These functions uses common calling functions i.e. the first argument is the value to be formatted, and the second argument is a template that defines the output or input format.
Read:
- nzsql command and its Usage
- Netezza Analytics Functions and Examples
- Explicit and implicit Netezza Type Casting With Examples
- Netezza Data Functions and Examples
- How to Resolve Netezza SPU Swap Partition Error
Netezza Cast Function
You can convert any data type to other using Netezza cast function.
CAST (<from-type> AS <to-type>);
from-type & to-type could be any data type.
Examples:
SYSTEM.ADMIN(ADMIN)=> select cast('123' as int) as int_col;
INT_COL
---------
123
(1 row)
Netezza to_char Function
Converts the specified value (which can be a date, timestamp, real, double precision, or numeric value) to a character string. The to_char function is one of very useful Netezza conversion function when you are working with miscellaneous data sets. Below is the syntax:
to_char(value,template);
Examples:
TRAINING.ADMIN(ADMIN)=> select to_char(date '2017-06-02', 'YYYY Month'); TO_CHAR ---------------- 2017 June (1 row) TRAINING.ADMIN(ADMIN)=> select to_char(timestamp '2017-06-02 20:19:07', 'Day, DD HH:MI:SS am'); TO_CHAR ---------------------------- Friday , 02 08:19:07 pm (1 row)
Netezza to_number Function
Converts a character string to a numeric value. Below is the syntax and example:
to_number(text,template);
Examples:
TRAINING.ADMIN(ADMIN)=> select to_number('12,345.6-', '99G999D9S');
TO_NUMBER
-----------
-12345.6
(1 row)
Netezza to_date Function
Converts a character string to a date. Below is the syntax and example:
to_date(text,template);
Examples:
TRAINING.ADMIN(ADMIN)=> select to_date('02 Jun 2017','DD Mon YYYY');
TO_DATE
------------
2017-06-02
(1 row)
Netezza to_date Function
Converts a character string to a timestamp. Below is the syntax and example:
to_timestamp(text,template);
Examples:
TRAINING.ADMIN(ADMIN)=> select to_timestamp('02 Jun 2017 08:30:55 am', 'DD Mon YYYY HH:MI:SS am');
TO_TIMESTAMP
---------------------
2017-06-02 08:30:55
(1 row)