CODEWITHSUNDEEP
sqlamazon-redshiftweek-number
RevivedPicard
RevivedPicard

Reputation: 129

calculate in redshift the week number of the year as weeknum() in excel

I need to calculate in redhsift sql the week number of the year starting from sunday to saturday and starting with the first of January but truncating the first week after the occurence of the first saturday. Kinda like WEEKNUM() works in excel:

so for example in 2020 it will be (mm/dd/yyyy)

while:

How can I achieve this?

Upvotes: 1

Views: 1987

Answers (1)

Andrea Reina
Andrea Reina

Reputation: 1240

This gives a week number that AFAICT follows the behavior of Excel and MSSQL:

  • weeks are sunday-saturday
  • first day of the year is always week 1
  • first/last days of the year are not necessarily full weeks
case when to_char(date_trunc('year', some_date), 'iw') = '01'
     then to_char(some_date + '1 day'::interval, 'iw')
     else to_char(some_date + '8 days'::interval, 'iw')
end

Upvotes: 1

Related Questions