CODEWITHSUNDEEP
amazon-redshift
Lucas Rezende
Lucas Rezende

Reputation: 584

How to SET DATEFIRST equal to Sunday in Amazon Redshift

2016-01-01 is the week 1 of 2016, but also is the week 53 of 2015.

When I run SELECT DATE_PART(w, '2016-01-01') it returns 53, but when I run SELECT DATE_PART(w, '2016-01-04') it returns 1.

Most probably this is happening because Redshift sets Monday as the day 1 of the week, and not Sunday, as it should be.

Not sure if this will solve the problem, but what I need is to make 2016-01-01 as week 1 and from 2016-01-03 to 2016-01-09 as week 2 and so on...

Upvotes: 3

Views: 4216

Answers (1)

vtuhtan
vtuhtan

Reputation: 1066

Create a custom function with this case statement or use it in your SQL statements:

case when DATE_PART('W', dt)>=51 and EXTRACT(DOY FROM dt) < 8 then 1 else DATE_PART('W', dt)+1 end week_no

It will start counting weeks on Jan 1st.

http://sqlfiddle.com/#!15/ccb90/9

Upvotes: 3

Related Questions