As 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.
