CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
AndyP
AndyP

Reputation: 607

How to get week number from date in this format YYYY-MON-DD in Redshift?

I have a date in this format YYYY-MON-DD. Is there any way to get week number from the date which is in that format in SQL Redshift?

select clientId, window_time as day, count(*) as total_cont from data;

Here day column is in this format YYYY-MON-DD. How can I get week number from date in this format YYYY-MON-DD?

Upvotes: 0

Views: 1506

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269284

From DATE_PART function - Amazon Redshift:

select date_part(w, listtime) as weeks, listtime
from listing where listid=10;

weeks |      listtime
------+---------------------
 25   | 2008-06-17 09:44:54
(1 row)

From TO_DATE - Amazon Redshift:

select to_date('02 Oct 2001', 'DD Mon YYYY');

to_date
------------
2001-10-02
(1 row)

Therefore, combine them together:

SELECT
  DATE_PART(w, TO_DATE(dt, 'YYYY-Mon-DD')) as week
FROM data

Upvotes: 1

Related Questions