Netezza PureData SQL Date Formatting Examples

Below is a SQL of various examples of IBM Netezza/PureData date formats, which may to be a useful reference.

SELECT

CURRENT_DATE,

—————–Sort Pattern1———————

TO_CHAR(CURRENT_DATE,’YYYYMMDD’) AS DATE_SORT_PATTERN1,

——————– Date Formats——————

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

TO_CHAR(CURRENT_DATE,’DD/MON/YY’) AS DATE_PATTERN2,

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

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

TO_CHAR(CURRENT_DATE,’MM.DD.YY’) AS DATE_PATTERN5,

TO_CHAR(CURRENT_DATE,’MM.DD.YYYY’) AS DATE_PATTERN6,

TO_CHAR(CURRENT_DATE,’MM/DD/YYYY’) AS DATE_PATTERN7,

TO_CHAR(CURRENT_DATE,’MM-DD-YY’) AS DATE_PATTERN8,

TO_CHAR(CURRENT_DATE,’MM-DD-YYYY’) AS DATE_PATTERN9,

TO_CHAR(CURRENT_DATE,’YY.MM.DD’) AS DATE_PATTERN10,

TO_CHAR(CURRENT_DATE,’YY/MM/DD’) AS DATE_PATTERN11,

TO_CHAR(CURRENT_DATE,’YY-MM-DD’) AS DATE_PATTERN12,

TO_CHAR(CURRENT_DATE,’YYYY.DDD’) AS YEAR_AND_DAY_NUMBER_OF_YEAR,

TO_CHAR(CURRENT_DATE,’YYYY.MM.DD’) AS DATE_PATTERN13,

TO_CHAR(CURRENT_DATE,’YYYY/MM/DD’) AS DATE_PATTERN14,

TO_CHAR(CURRENT_DATE,’YYYY-MM-DD’) AS DATE_PATTERN15,

TO_CHAR(CURRENT_DATE,’DD-MON-YY’) AS DATE_PATTERN5,

——————–Parts of Date——————

TO_CHAR(CURRENT_DATE,’YYYY’) AS FOUR_DIGIT_YEAR,

TO_CHAR(CURRENT_DATE,’YY’) AS YEAR_OF_CENTURY,

TO_CHAR(CURRENT_DATE,’J’) AS JUALIAN_CURRENT_DATE,

TO_CHAR(CURRENT_DATE,’Q’) AS CALENDAR_QUARTER_OF_YEAR,

TO_CHAR(CURRENT_DATE,’WW’) AS CALENDAR_WEEK_OF_YEAR,

TO_CHAR(CURRENT_DATE,’DDD’) AS CALENDAR_DAY_OF_YEAR_NUMBER,

TO_CHAR(CURRENT_DATE,’W’) AS WEEK_OF_MONTH,

TO_CHAR(CURRENT_DATE,’DAY’) AS NAME_OF_DAY_OF_WEEK,

TO_CHAR(CURRENT_DATE,’DY’) AS ABBREVIATED_DAY_OF_WEEK,

TO_CHAR(CURRENT_DATE,’MM’) AS NUMERIC_MONTH_OF_YEAR,

TO_CHAR(CURRENT_DATE,’MON’) AS ABBREVIATED_MONTH_OF_YEAR

FROM _V_DUAL;