Extracting Day of Week as an Integer with Netezza SQL

Question

This should be doable, but how can I extract the day of the week from a field containing data in date format with Netezza SQL? I can write the following query:

SELECT date_part('day',a.report_dt) as report_dt FROM table as a

but that gives me the day of the month.

thanks for any help

Prine

Answer 1

The below queries give day numbers for any week,month,year for a particular date.

--Day of Week 
SELECT EXTRACT(dow FROM report_dt) FROM table;
--Day of Month
SELECT DATE_PART('day', report_dt) FROM table;
--Day of Year
SELECT EXTRACT(doy FROM report_dt) FROM table;

Slovak

+1 To add, week starts from Sunday. 1=Sunday, 7=Saturday. That's unlike java where week starts from Monday. – Esqueda

Answer 2

Netezza is just ANSI SQL, originally derived from PostgreSQL. I'd expect this to work.

select extract(dow from a.report_dt) as report_dt
from table as a

Returns values should range from 0 to 6; 0 is Sunday. You might expect that to be an integer, but in PostgreSQL at least, the returned value is a double-precision floating point.

Cordeiro

Answer 3

If you want to extract directly the day name :

Select to_char(date, 'Day') as Day_Name From table;

Lavernalaverne

Answer 4

In Netezza SQL, SELECT EXTRACT(dow FROM report_dt) would return values 1 to 7. 1 is Sunday, 7 is Saturday.

Concerned