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.
Week number with If elseasked 3 months ago
How to set Max number of replies in lambda elicitSlotasked 4 years ago
Trying to port a number to connectasked 3 years ago
I'm trying to find a way to convert two booleans into a case when expressionAccepted Answerasked 14 days ago
How to make a Year-Over-Year Line Chart in QuickSightasked 8 months ago
In cloudwatch dashboards how can I get the number widget to update correctly while I change the time range on the top?Accepted Answerasked a month ago
Invalid Week number returned
Redshift serverless: error while trying to create an external tableasked 3 months ago
Interval values with month or year parts are not supported
One phone number for Amazon Connect AND PinpointAccepted Answerasked 9 months ago