Netezza Date Format and Conversions

Date data types are used to store the date and time fraction values. Netezza Date format includes four data types, and are used to store the date with time details:

• DATE = for year, month, day storage.
• TIME = for hour, minute, second, fraction with (includes 6 decimal positions).
• TIME WITH TIME ZONE / TIMETZ = same as TIME, also includes time zone information.
• TIMESTAMP = for year, month, day, hour, minute, second, fraction ( includes 6 decimal positions).

Netezza Date Format Conversion

Netezza can convert quoted date strings values to date time data. IBM Netezza accepts different date formats, including ISO date time strings, and you can also specify the cast operator such as ::date, ::time, ::timestamp after the date string literal value to convert them to proper date format. You can use the Netezza SQL date format functions to convert the string literals, integer, date/time etc to required format.

Read:

• Netezza Date Functions and Examples
• nzsql Command and its Usage
• Netezza String Functions and Its Usage With Examples

Convert a String to Date format using TO_DATE() Function

TRAINING.ADMIN(TRAINEE)=>Select TO_DATE( '2009-12-23 23:45:58','YYYY-MM-DD HH24:MI:SS'); 
TO_DATE 
------------ 
2009-12-23 
(1 row)

Convert a String to timestamp format using TO_TIMESTAMP() Function

TRAINING.ADMIN(TRAINEE)=> Select to_timestamp('05 Dec 2016', 'DD Mon YYYY'); 
TO_TIMESTAMP 
--------------------- 
2016-12-05 00:00:00 
(1 row)

Netezza Date Format Conversions Examples

Convert Netezza timestamp to YYYYMMDD format:

TRAINING.ADMIN(TRAINEE)=> select TO_CHAR(TO_DATE( '2016-09-22 22:30:58','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDD'); 
TO_CHAR 
---------- 
20160922 
(1 row)

Convert Netezza YYYYMMDD to date format:

TRAINING.ADMIN(TRAINEE)=> select date(to_char(20160922,'99999999')); 
DATE 
------------ 
2016-09-22 
(1 row)

Using Netezza DATE and DATETIME variables in SQL statements

The type of the SQL parameter must match the type of the column in the database table.

For examples, you cannot use a DATE variable when the target column is a DATETIME. You have to explicitly type cast the values in order to perform the comparison. You can also use :date, ::time, ::timestamp after values value to convert it to proper format.

For example,

select CASE WHEN col1::date = col2 then “Mathing” ELSE “Not Matching” END FROM sometable;