Let's Talk

Let's Talk.

Looking for our offices? View locations.

Talking Tech: Overview of NetSuite Formulas: TO_DATE & TO_CHAR Functions

Let’s Talk Tech.

Accordion’s “Talking Tech” series explores how different CFO Technology solutions can empower finance functions to support organizational strategic initiatives – by implementing business process recommendations, optimizing operations, and capitalizing on value creation opportunities.

Now, let’s take a look at how to use two of the most popular NetSuite functions: TO_DATE and TO_CHAR.

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.

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

About the Author

Joe Son
Joe Son
Managing Director

Joe is a Managing Director within CFO Technology’s Enterprise Resource Planning (ERP) pillar. He has nearly two decades of NetSuite implementation & customization experience for companies ranging from Fortune 500 to small LLCs.  Read more

Need NetSuite Support? Let's Talk.