NetSuite: Getting Week Numbers Using the TO_CHAR Function

Using a Saved Search to get a week number of a date can be tricky. A day like January 5, 2022 could return week 1, but you expect it to be in week 2. So why could that be?

There are a few TO_CHAR date formats to remember when getting the week number.

• Week of Year (WW) - Defines the first week as the first 7 days of the year.
• ISO Week of Year (IW) - Defines the first week as the first Monday, plus the next 6 days. Everything before the first Monday is considered either week 52 or 53 of the previous year.
• ISO Week of Year (IY) - Used in relation to 'IW', for better identification of what year the ISO week refers to.

For most purposes, you would want to use the ISO week, plus the ISO year. If that is the case, you can use the following formula:

to_char({custrecord12},'IW - IYYY')

Be sure to replace '{custrecord12}' with the appropriate date value. Below are examples of how results of each date format differ.

Results tab:

Search Results:

An Interesting Case

I once had a customer that asked about calendar years with over 366 days. You may think that'd be absurd, but I was fortunate enough to learn a few months prior that a year does not equal 365.25 days exactly (it's 365.24219). Meaning, the correction that leap days provide is not accurate enough, that in time, our calendars would be off by 1 day with respect to the Earth's revolutions.

I knew then that there could be 2 extra days in a year, given enough time. But the extra 2 days in a year would only occur once every 3000 years. And the consensus is to simply skip a leap day every 3000 years. That's a lot easier than having 2 leap days every 400 years. How we decide which leap day to skip, is irrelevant for now. We'll cross that bridge when we get there.

Also, this is apart from the mishaps caused by people shifting to the Gregorian calendar (like what happened in 1582 and Sweden in 1712). The original question was about the calendar, moving forward. By the way, you can't have valid date values in NetSuite prior to 4/1/1601. Nor can you have a February 30 (like the Sweden case). The workaround for that is to use text values then convert them using TO_DATE. Interesting side note: entering lost dates in October 1582 are actually automatically converted to October 15; February 30, 1712 still gives an error though.

So to respond to the customer, I told him that I actually knew about how leap years aren't exact. It seems he knew about it too. I explained to him what the general consensus is, and the workarounds I mentioned above. He seemed satisfied about the answer. I never thought that that piece of trivial knowledge would ever come in handy. I guess I was wrong.