<img src="https://ws.zoominfo.com/pixel/5BFMW73xT6Cu70sN1cUt" width="1" height="1" style="display: none;">

NetSuite Saved Searches: What is the 'TO_CHAR' Formula?

Posted by Jesse Guzman, Consultant on Dec 31, 2019 5:00:00 PM
Jesse Guzman, Consultant
Find me on:

pexels-photo-106344As you may have caught on by our previous blog posts, we really love NetSuite saved searches at Concentrus.  Saved searches have many uses and are one of the easiest tools to learn and use in NetSuite.  It doesn’t matter if you are an everyday user or log in occasionally to get the information you need, saved searches give you the tools to get the data you need, how you need it and when you need it.

My colleague Sarah Meehan has a great article on “5 Ways to Use Formulas in NetSuite’s Saved Searches”. In this post, we’re going to talk about  specific formula used to transform data to many different formats, and for this blog we’ll focus on dates and times.

Each user has the option to set date and time format under “User Preferences” which is a great tool that allows for personalization of your environment. 

 

However, your preferred date/time format may not be the preferred format to a customer or vendor; fortunately NetSuite provides an easy way to format dates/times.

Before we jump into TO_CHAR, we’ll briefly explain the difference between that and another very common formula called TO_DATE which has a similar result but under different circumstances.

TO_DATE is used to convert date/time formats when the original input of data is a text/numeric version and now you want it to be a date format.

TO_CHAR, on the other hand, is used to take a system date and convert is as you please.  For the purpose of this blog we’ll focus on TO_CHAR.

I’ve included a simple table that is available on SuiteAnswers ID 50499.  The table provides the parameters that can be used on the TO_CHAR formula to convert to the desired output.

For the purposes of this example we’ll use the following data:

Date: Monday, December 30, 2019

Time: 02:26:54 PM

Parameter

Formula

Sample Result

Explanation

DL

To_Char({today},'DL')

Monday, December 30, 2019

Long version of the date including day of the week, month, day and year.

YEAR

To_Char({today},'YEAR')

TWENTY NINETEEN

The Year spelled out

YYYY

To_Char({today},'YYYY')

2019

4-digit year

YYY

To_Char({today},'YYY')

019

Last 3, 2, or 1 digit(s) of year.

YY

To_Char({today},'YY')

19

Y

To_Char({today},'Y')

9

IYY

To_Char({today},'IYY')

020

Some organizations use an ISO week date system, this formula returns the year according to ISO standard.

IY

To_Char({today},'IY')

20

I

To_Char({today},'I')

0

IYYY

To_Char({today},'IYYY')

2020

4-digit year based on the ISO standard

Q

To_Char({today},'Q')

4

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

To_Char({today},'MM')

12

Month (01-12; JAN = 01).

MON

To_Char({today},'MON')

DEC

Abbreviated name of month.

MONTH

To_Char({today},'MONTH')

DECEMBER

Name of month, padded with blanks to length of 9 characters.

RM

To_Char({today},'RM')

XII

Roman numeral month (I-XII; JAN = I).

WW

To_Char({today},'WW')

52

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

To_Char({today},'W')

5

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

To_Char({today},'IW')

1

Week of year (1-52 or 1-53) based on the ISO standard.

D

To_Char({today},'D')

2

Day of week (1-7). Note the first day of the week is setup at the time of implementation.  For this example, Sunday is the first day of the week.

DAY

To_Char({today},'DAY')

MONDAY

Name of day.

DD

To_Char({today},'DD')

30

Day of month (1-31).

DDD

To_Char({today},'DDD')

364

Day of year (1-366).

DY

To_Char({today},'DY')

MON

Abbreviated name of day.

J

To_Char({today},'J')

2458848

Julian day; the number of days since January 1, 4712 BC.

HH

To_Char({today},'HH')

02

Hour of day (1-12).

HH12

To_Char({today},'HH12')

02

Hour of day (1-12).

HH24

To_Char({today},'HH24')

14

Hour of day (0-23).

MI

To_Char({today},'MI')

26

Minute (0-59).

SS

To_Char({today},'SS')

54

Second (0-59).

SSSSS

To_Char({today},'SSSSS')

52014

Seconds past midnight (0-86399).

The important things to consider when using the TO_CHAR formula is the format the source data is in.  For example, you cannot reference the ‘TRANSDATE’ field if you want to include a time value in the results as the field ‘TRANSDATE’ does note store time values and will therefore return zeros.

Assuming the date is 12/30/19 and 2:26:54 PM

  • TO_CHAR({TRANSDATE} ,'YYYYMMDD HH24MISS') would return 20191230 000000
  • TO_CHAR({custbody_timestamp} ,'YYYYMMDD HH24MISS') would return 20191230 142654

To include separators, simplely include them in the formula as so:

  • TO_CHAR({custbody_timestamp} ,'YYYY-MM/DD HH24:MI.SS') would return 2019-12/30 14:26.54

 *Note: the time changed since the first execution.

Want to learn more about “TO-CHAR” formula, or get guided support on saved searches? Contact Concentrus today to discuss your reporting needs.

Contact Us Today! 

Tags: NetSuite, NetSuite How To's, NetSuite Tips