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 a 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
