We help IT Professionals succeed at work.

Netezza SQL - get the day of the week from a field

4,072 Views
Last Modified: 2012-07-03
Can someone help me with the code below?  What I need is the number of the day of the week from the report_dt field so I can use it to do some aggregations later.  Basically, if its a monday, give me 1.  the data in the report_dt field is in a date format (1/1/2012) etc.

SELECT date_part('day',report_dt) as report_dt
FROM table
Comment
Watch Question

Top Expert 2012

Commented:
If you can accept Sunday=1 instead of Monday=1 you can use the dayofweek() function (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek)
Top Expert 2012

Commented:
If you must have Monday=1 though, then a little workaround will do the trick

select if((dayofweek(report_dt)-1)=0, 7, dayofweek(report_dt)-1) as report_dt
from table

Basically, use the dayofweek function, subtract 1 so that Monday will be 1, then I guess you want Sunday to be 7 so if dayofweek(report_dt)-1 = 0, replace it with seven, else give the result of dayofweek(report_dt)-1
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.