6

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

user1205546
  • 61
  • 1
  • 1
  • 2

4 Answers4

10

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;
Teja
  • 13,214
  • 36
  • 93
  • 155
  • 1
    +1 To add, week starts from Sunday. `1=Sunday, 7=Saturday`. That's unlike java where week starts from Monday. – zengr Aug 09 '16 at 18:59
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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
1

If you want to extract directly the day name : 

Select to_char(date, 'Day') as Day_Name From table;
Grabault
  • 41
  • 1
  • 6
0

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

TheBaker
  • 83
  • 8