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
- Netezza/PureData SQL Date Formatting Examples
- Netezza / PureData – how to convert an integer to a date
- Netezza / PureData – how to set processing date to next day based on a timestamp
- Netezza/PureData – How to add days to a date field
- PureData – Now() Command For Current Date
- PureData – Current Date Function