Netezza / PureData – how to convert an integer to a date

Converting an Integer to a Date

Converting an Integer to a Date

Lately, I have been encountering a lot of date fields stored as integer.  So, I thought it might be useful to write a quick post on how to convert an date stored as an integer to a date.   The process is quite easy and intuitive, here are a few approaches for doing so.

Basic To_Date Command Format

  • to_date(<<IntegerField>>,'<<Format>>’)

Example Integer Converted Directly Using To_Date Method

  • select to_date(20090731,’YYYYMMDD’) as Integer_As_Date;

To_Char Method

I have seen the to_char method used, but this approach is not usually necessary.

  • Select To_Date(20170303, ‘YYYYMMDD’) as Integer_Value_To_Date_Method;

Cast Method

I have seen the cast method used, but, again, this approach is not usually necessary.

  • Select To_Date(Cast(date_skry as Varchar(10)), ‘YYYYMMDD’) as Integer_Cast_Varchar_To_Date_Method

Integer Value To_Date Method

  • Select To_Date(20170303, ‘YYYYMMDD’) as Integer_Value_To_Date_Method;

Adding day element to integers with only year and month

This approach can be helpful when integer dates only provide the year and month.

  • select to_date(201004||’01’,’YYYYMMDD’)

Example integer To Date Conversion SQL

SELECT date_skry as Integer_date_skry

, To_Date(date_skry,’YYYYMMDD’) as Integer_To_Date_Method

, To_Date(to_char(date_skry,’99999999′), ‘YYYYMMDD’) as Integer_To_Char_To_Date_Method

, To_Date(Cast(date_skry as Varchar(10)), ‘YYYYMMDD’) as Integer_Cast_Varchar_To_Date_Method

, To_Date(20170303, ‘YYYYMMDD’) as Integer_Value_To_Date_Method

, To_Date(to_char(20170303,’99999999′), ‘YYYYMMDD’) as Integer_Value_To_Char_To_Date_Method

FROM Blog.DataMart.date_dim  where date_skry = 20170303

 

Related References

Cast Conversions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Functions and operators Functions, Cast conversions

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations, SQL

 

Functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Functions and operators, Functions

 

Summary of Netezza casting

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, SQL statement grammar, Explicit and implicit casting, Summary of Netezza casting

 

Conversion Functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Conversion Functions