While working on a recent data conversion a data timestamp error occurred, where the legacy code and data was ‘0000-00-00’ default stamp, which was causing errors in Netezza/PureData Analytics (PDA). ‘0000-00-00’ is not a valid timestamp in Netezza and will produce a ‘ ERROR [HY000] ERROR: Bad timestamp external representation ‘0000-00-00’’ notice.
When handling default dates in SQL and ETL’s, generally speaking, ‘1901-01-01’ or ‘0001-01-01’ as use as defaults for null and/or invalid dates. Both of these dates and or timestamps will insert into Netezza, which I used a quick, simple, proof table to demonstrate, which were SQL extracted and in the table below.
Valid Defaults dates in Netezza
- PureData / Netezza – What date/time ranges are supported by Netezza?
- Netezza / PureData – How to convert a timestamp to date in SQL
- Netezza / PureData – Two ways to get Numeric Day of Year
- Netezza /PureData Date – Difference in Days SQL
- PureData / Netezza – What date ranges are supported by Netezza?
Temporal data types
PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types
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
PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions