Question & Answer
Question
How do I create day of week (dow) functionality?
Answer
Netezza does not support a 'dow' function when selecting column data from tables. It does allow you to use 'dow' when using literals, but this is simply leftover functionality from Postgres. For example:
select date_part('dow', date '1999-03-03') from d_date;
--works fine
select date_part('dow', date cal_dt) from d_date;
--ERROR: Bad date external representation 'cal_dt' (State:HY00,Native Code:6C)
You could achieve the desired result with several other methods. The following method offers the best performance:
SELECT ( - date '1900-01-01' ) % 7 + 1 FROM <table>;
You can replace the with a real column name, and it will work . Also, if you want to start counting from a day other than Sunday, you can adjust the seed date '1900-01-01' to a date other than Sunday, e.g. '1900-01-02' to start from a Monday. Also, this will return a number from 1-7. If you would rather 0-6, simply remove the "+1".
Historical Number
NZ045186
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21569867