Search code examples
netsuitesaved-searches

using To_CHAR function in NetSuite

I'm using TO_CHAR function, but having issue dates ranges between this year to next year.

For example following formula works when I use formula: case when to_char({shipdate},'DD/MM/YYYY') between '10/01/2018' and '12/24/2018' then {quantity} end

But I do not get any results when I use formula with date ranging across two years: case when to_char({shipdate},'DD/MM/YYYY') between '12/25/2018' and '02/24/2019' then {quantity} end

Can you please advise why it's not working?

Also, second question is regarding date format. By default formula in suite answers uses 'DD/MM/YY' but we use the date format of 'MM/DD/YYYY' so I want to confirm that I should be updating the formula format to say 'MM/DD/YYYY'?

Thanks!

Solution

  • I was able to use to_date function to achieve the results: case when {type} = 'Sales Order' and {shipdate} between to_date('01/01/2017', 'MM/DD/YYYY') and to_date('12/31/2017', 'MM/DD/YYYY') then {quantity} else 0 end