NetSuite Saved Search | Pull first day of week based on date

Question

I am hoping to accomplish grabbing the first day of the current week based on a provided date field using a formula. Until now I've used the following to pull the week of the month, but we are looking for the specific first day of that week:

'Week ' || TO_CHAR({shipdate},'w') || ' of ' || TO_CHAR({shipdate},'MON')

Using ship date of 10/19/2023 as my example, the above formula will display something like 'Week 3 of OCT'. But now I want it to show 'Week of 10/16'. So my best guess on how to accomplish this is something like:

'Week of ' || TO_CHAR({shipdate},'mm-w')

But that result shows something like 'Week of 10-3'... Please help!

Answer 1

The second parameter of the TO_CHAR() function provides formatting only - it does not make any calculation on the date passed in as the first parameter.

To calculate the date of the Monday of the same week as the Ship Date, you can use

TO_DATE({shipdate} - TO_CHAR({shipdate}, 'D') + 2)

In this formula, the TO_CHAR({shipdate}, 'D') returns the day-of-week of the ship date. Subtracting this from the ship date gives a date which will correspond with the last day of the previous week. Then adding 2 brings us back to Monday (the second day of the week). Note: In some databases, Monday is the first day of the week. I'm not sure if there are regional settings available in NetSuite that would cause this, but if so, and if your account has such settings, you may need to replace the '2' with '1'.

Now that you have the correct date to work with, you can apply formatting to return the related string:

TO_CHAR(TO_DATE({shipdate} - TO_CHAR({shipdate}, 'D') + 2, 'MM/DD/YYYY'), '"Week of" MM/DD')

A couple notes on this:

1. I've explicitly added the format mask 'MM/DD/YYYY' to the original TO_DATE formula, simply because it's good practice to do so.
2. The format mask for the outside TO_CHAR formula includes "Week of". The Oracle date format models allow any quoted text (text in double quotes, that is) to be included as part of the formatted string, so this is a good way to avoid having to concatenate multiple strings for your result.