DataStage – Timestamp from Datetime

Timestamp from date is one of those data type conversions, which I occasionally have to do in DataStage but can never seem to remember. So, I thought I would write this quick post to document the data type conversion code, which is easy, once I finally remember how to do it again.  The TimestampFromDateTime Function … Continue reading DataStage – Timestamp from Datetime

DataStage – Timestamp from date

This is one of those data type conversions which I occasionally have to do in DataStage, but can never seem to remember. So, I thought I would write this quick post to document  the data type conversion code, which is really easy, once I finally remember how to do it again.  The TimestampFromDateTime  Function DataStage … Continue reading DataStage – Timestamp from date

PureData / Netezza – What date/time ranges are supported by Netezza?

Here is a synopsis of the temporal ranges ( date, time, and timestamp), which Netezza / PureData supports. Temporal Type Supported Ranges Size In Bytes Date A month, day, and year. Values range from January 1, 0001, to December 31, 9999. 4 bytes Time An hour, minute, and second to six decimal places (microseconds). Values … Continue reading PureData / Netezza – What date/time ranges are supported by Netezza?

SFDC – Using a timestamp literal in a where clause

Working with timestamp literals in the Infosphere SFDC Connector soql is much like working date literals.  So, here a quick example which may save you some time. SOQL Timestamp String Literals Where Clause Rules Basically, the timestamp pattern is straight forward and like the process for dates, but there are some differences. The basic rules … Continue reading SFDC – Using a timestamp literal in a where clause

Netezza / PureData – How to convert a timestamp to date in SQL

  Since timestamps are integer, which are numbers, the date function can easily convert a timestamp to a date in Netezza SQL. Date Function Arguments, per IBM Documentation First argument data type Second argument data type Returns date numeric(8,0) date Example Timestamp to Date Conversion SQL In this example CREATEDATE is stored as a timestamp, … Continue reading Netezza / PureData – How to convert a timestamp to date in SQL

Netezza / PureData – ERROR [HY000] ERROR: Bad timestamp external representation ‘0000-00-00’

Bad timestamp external representation '0000-00-00' 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 … Continue reading Netezza / PureData – ERROR [HY000] ERROR: Bad timestamp external representation ‘0000-00-00’

Netezza / PureData – How to convert an epoch field to a timestamp

How To Convert And Epoch Field To A Timestamp This is one of the items, which we need to do in Netezza SQL from time to time, but is not exactly obvious to the average SQL user.  So, having a pattern for making the conversion for an epoch field to a timestamp can be a … Continue reading Netezza / PureData – How to convert an epoch field to a timestamp