Bert Swope
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
| TEST_SK | TEST_TS |
| 1 | 1901-01-01 00:00:00 |
| 2 | 00:00:00 |
Related References
- 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?