CODEWITHSUNDEEP
sqlamazon-redshift
rcr
rcr

Reputation: 152

Amazon - Redshift : Week number for a given date is coming wrong

Considering Sunday as the first day of the week the below statements in redshift should return 2 as the week number instead returning 1.

Mysql has a function called Week() where we can use the mode to get the desired result. Likewise is there any function in redshift that I could use to get the correct week number.

Any help would be really appreciated.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2018-01-07');

SELECT TO_CHAR(TIMESTAMP '2018-01-07','WW');

Upvotes: 5

Views: 22238

Answers (3)

Leutecia
Leutecia

Reputation: 145

I had the same issue while converting mysql to redshift. This is how I fixed it.

TO_CHAR(DATE_TRUNC('week', your_date + INTERVAL '1 day') - INTERVAL '1 day', 'ww')::INTEGER

Upvotes: 0

Himal
Himal

Reputation: 171

date_part can be directly used for your use-case.

Query:

select date_part(w, '2018-01-07');

Here's a link for more details: https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_PART_function.html

Upvotes: 5

Shailesh
Shailesh

Reputation: 2286

There seems to be no direct solution to this, but here's a workaround: 

SELECT EXTRACT(WEEK FROM TIMESTAMP '2018-01-07'::date + '1 day'::interval);

Source

Upvotes: 1

Related Questions