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
Below are sample outputs returned by saved search.
Date Functions result 1
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!