NetSuite Saved Search Formula – TO_DATE and TO_CHAR Date Function

NetSuite Saved Search Date functions can be used in the Search Formula to convert and extract elements of Date Object. It is one of the popular formula functions available.  While there are lot of Date functions at your disposal in NetSuite, we are going to focus on TO_DATE and TO_CHAR functions. TO_DATE function is specifically used to convert text value of date into date object.  TO_CHAR function is used to convert date object into a text representation of it.  TO_CHAR function is unique because it can be used to convert other data types (not just Date object) into text.

Date Formats used for Date functions

Date format is a date pattern the system uses when converting from/to Date object. While Oracle SQL provides many different Date format elements, below are list of most popular ones you can use with NetSuite:
Oracle Format Models > Datetime Format Elements section has full list of available formats.

Format Description
MM Numeric month; 1 through 12
MON Abbreviated month name; Jan, Feb, Jul, Dec etc.
MONTH Full month name; January, July, June etc.
DD Numeric day of month; 1 through 31
DY Abbreviated name of day; Fri, Sat, Sun etc.
YYYY 4 digit year; 2016, 2015
AM or PM Meridian indicator.
HH or HH12 Numeric value for Hour of day; 1 through 12
HH24 Numeric value for Hour in 24 hours; 0 through 23
MI Numeric value for Minute; 0 through 59
SS Numeric value for Second; 0 through 59

Below are list of sample codes used in formula to convert string to date and date to string:

TO_DATE('11/16/2016','MM/DD/YYYY')

TO_DATE('16-11-2016','DD-MM-YYYY')

TO_DATE('Nov 5, 2016','MON DD, YYYY')

TO_DATE('November 5 2016', 'MONTH DD YYYY')

TO_CHAR({datecreated},'DY, MM/DD/YYYY')

TO_CHAR({datecreated},'MONTH, YYYY')

TO_CHAR({datecreated},'MM/DD/YYYY HH:MI:SS AM')

All samples above returns a value when executed in NetSuite Saved Search.

Date functions; TO_CHAR and TO_DATE
Date functions; TO_CHAR and TO_DATE

Below are sample outputs returned by saved search.

Date Functions result 1
Date Functions result 1
Date Functions result 2
Date Functions result 2

Debugging Help

  • Always make sure formula type matches the value you are returning. For example, Formate (Date) and Formula (Datetime) is not the same.
  • Always provide Format Element when converting from/to Date.
  • When converting to Date, value will be returned in Date Format you have set in your personal preference. For example, if your NetSuite personal preference setting is set to show dates in MM/DD/YYYY format, even if you ran TO_DATE(’16-11-2016′,’DD-MM-YYYY’) as your formula, value will show as 11/16/2016
  • If you have complex nested

Feel free to contact us if you need help building out complex reports in NetSuite!

About Abaci
Abaci is a premier NetSuite Solution Provider with deep knowledge in both business process and technology architecture. Our team is passionate about NetSuite and has an exclusive commitment to NetSuite and to providing the highest level of system expertise on its full suite of cloud-based business management applications.