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

I use the TimestampFromDateTime(%date%,%time%) function to do this data type conversion. I’m sure there are other ways to achieve the result, but I find this method clean and easy to perform. The TimestampFromDateTime(%date%,%time%) function is in the Functions > Date & Time menu.

To populate the function, you need only add your date field on use ’00:00:00’ as your time element

Time Element example

TimestampFromDateTime(<<Date Field Here>>, ’00:00:00′)

Example transformer code With Date Field

TimestampFromDateTime(Lnk_10.POSTED_DATE, ’00:00:00′)

Netezza / PureData Date – Difference in Days SQL

Netezza PureData Date Difference in Days SQL, Subtracting Non-inclusive dates, Subtracting inclusive dates
Netezza /PureData Date Difference in Days

Since Netezza does not have a datediff function, the ‘old school’ of calculating the difference, in days, between dates must be used.

Subtracting Inclusive Dates

To subtract to day and include end date, as a day, in calculation (1 day is added)

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates

From _v_dual;

 

Subtracting Non-inclusive dates

To subtract dates non-inclusive simply subtract the dates

select date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Example SQL From Graphic

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates,

date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Related References

Netezza / PureData – How to Number for day of week in SQL?

Netezza / PureData - Numeric Day of Week
Netezza / PureData – Numeric Day of Week

I had reason today to get the number of the day of the week, in PureData / Netezza, which I don’t seem to have discussed in previous posts.  So, here is a simple script to get the number for the day of week with a couple of flavors, which may prove useful.

Basic Format

select extract(dow from <<FieldName>>) from <<SchemaName>>.<<tableName>>

Example SQL

 

SELECT

CURRENT_DATE

,  TO_CHAR(CURRENT_DATE,’DAY’) AS DAY_OF_WEEK

—WEEK STARTS ON MONDAY

,  EXTRACT(DOW FROM CURRENT_DATE)-1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_MONDAY

—WEEK STARTS ON SUNDAY

,  EXTRACT(DOW FROM CURRENT_DATE) AS DAY_OF_WEEK_NUMBER_STARTS_ON_SUNDAY

—WEEK STARTS ON SATURDAY

,  EXTRACT(DOW FROM CURRENT_DATE)+1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_SATURDAY

FROM _V_DUAL;

 

Related References

Extract date and time values

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

Date/time functions

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

Template patterns for date/time conversions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Conversion functions, Template patterns for date/time conversions

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_templ_patterns_date_time_conv.html

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

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

SQL (Structured Query Language),  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 ‘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_SKTEST_TS
11901-01-01 00:00:00
2 00:00:00

Related References

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

Date/time functions

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

Infosphere Datastage – Useful Date Transformations

Infosphere IIS Datastage,  Useful Date Transformations
Infosphere Datastage – Useful Date Transformations

Here are few Datastage date transformation, which I have found useful, many of these can Also be accomplished in SQL, if sourcing your data from an RDBMS.

Useful Date Transformations

ItemDescriptionResult
TomorrowDateFromDaysSince(1, CurrentDate()) 
YesterdayDateFromDaysSince(-1, CurrentDate()) 
Convert date to string with dashesDateToString((<< Date_Field or CurrentDate() >>,”%,”%YYYY-%MM-%DD”)2011-11-02
Convert date to string without dashesDateToString(<< Date_Field or CurrentDate() >>,”%YYYY%MM%DD”)20111102
Get short month nameDateToString(<< Date_Field or CurrentDate() >>,”%,”%mmm”)Feb
Get long month nameDateToString(<< Date_Field or CurrentDate() >>,”%mmmm”)February
Get short weekday nameDateToString(<< Date_Field or CurrentDate() >> date_value,”%eee”)Tue
Get long weekday nameDateToString(<< Date_Field or CurrentDate() >> ,”%eeee”)Tuesday
Irregular Date Format (months and days can be 1 or 2 digits)StringToTimestamp((<>,”%m/%d/%yyyy”)2011-11-02

Useful SQL Date Transformation References

Oracle SQL Date Formatting Examples

Oracle Database, SQL Date Formatting Examples, TO_CHAR
Oracle Database – SQL Date Formatting Examples

Below is a SQL of various examples of Oracle date formats, which I have found to be a useful reference.

Select

SYSDATE,

TO_CHAR(SYSDATE,’YYYY-MM-DD AD’) AS ANNO_DOMINI,

TO_CHAR(SYSDATE,’YYYYMMDD’) AS SORT_PATTERN1,

TO_CHAR(SYSDATE,’YYYY/MM/DD’) AS DATE_PATTERN1,

TO_CHAR(SYSDATE,’DD/MM/YYYY’) AS DATE_PATTERN2,

TO_CHAR(SYSDATE,’DD/MON/YYYY’) AS DATE_PATTERN3,

TO_CHAR(SYSDATE,’DD-MON-YY’) AS DATE_PATTERN4,

TO_CHAR(SYSDATE,’CC’) AS CENTURY,

TO_CHAR(SYSDATE,’YYYY’) AS FOUR_DIGIT_YEAR,

TO_CHAR(SYSDATE,’YY’) AS YEAR_OF_CENTURY,

TO_CHAR(SYSDATE,’J’) AS JUALIAN_DATE,

TO_CHAR(SYSDATE,’Q’) AS CALENDAR_QUARTER_OF_YEAR,

TO_CHAR(SYSDATE,’WW’) AS CALENDAR_WEEK_OF_YEAR,

TO_CHAR(SYSDATE,’DDD’) AS NUMBER_OF_CALENDAR_DAY_OF_YEAR,

TO_CHAR(SYSDATE,’W’) AS WEEK_OF_MONTH,

TO_CHAR(SYSDATE,’DAY’) AS NAME_OF_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’DY’) AS ABBREVIATED_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’MM’) AS NUMERIC_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MON’) AS ABBREVIATED_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MONTH’) AS SPELLED_OUT_MONTH_OF_YEAR

FROM DUAL;

Related References