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
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 … Continue reading Netezza / PureData Date – Difference in Days SQL
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 … Continue reading Netezza / PureData – How to Number for day of week in SQL?
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 … Continue reading Netezza / PureData – how to convert an integer to a date
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’
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 ItemDescriptionResultTomorrowDateFromDaysSince(1, CurrentDate()) YesterdayDateFromDaysSince(-1, CurrentDate()) Convert date to string with dashesDateToString((<< Date_Field or CurrentDate() >>,"%,"%YYYY-%MM-%DD")2011-11-02Convert date to string without dashesDateToString(<< Date_Field or … Continue reading Infosphere Datastage – Useful Date Transformations
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. SelectSYSDATE,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') … Continue reading Oracle SQL Date Formatting Examples