- Newest
- Most votes
- Most comments
According to timeanddate.com https://www.timeanddate.com/calendar/custom.html?year=2022&month=1&country=22&typ=1&wno=1&display=1&df=1 Week 1 of 2022 starts on 3/Jan/2022 so Query 1 and 2 are responding correctly.
Whereas in Query 3 you it is returning what you originally wanted i.e. "I am trying to find a way to return the week number as 1 when date is 1st Jan each year" Per the definitions listed in the table at https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html WW - Week number of year (1–53; the first week starts on the first day of the year.) So for your use case Query 3 might be the way to go as it returns exactly what you want.
Kishan, thank you for the quick response.
After going through the links, I see the week number 52 returned for 'Query 1' and 'Query 2' is due to ISO-8601 standard, where years always start on the first Monday closest to January 1st. This may mean that the year does not start until January 4th in some cases, or may start in late December in others. However standard Gregorian calendars always start the year on the 1st of January.
To fix I use "to_char(replace_with_date_value,'IW')" for ISO week numbers, and "to_char(replace_with_date_value,'WW')" for standard Gregorian calendars, which starts from 1-52, counting from first day of the year.
Relevant content
- asked 2 years ago
- asked 2 months ago
- Accepted Answerasked 10 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago