When managing data in SQL—especially within environments like NetSuite’s SuiteAnalytics or custom reporting—it’s common to work with timestamps. However, raw date and time values are not always human-friendly or ready for presentation. That’s where the TO_CHAR function becomes essential.
With TO_CHAR, you can convert DATE or TIMESTAMP values into a formatted string using specific datetime format elements. Whether you need a precise timestamp, the day of the week, or even the current quarter, TO_CHAR gives you full control over how the data is displayed.
Below is a sample query that demonstrates several useful datetime format elements:
SELECT
TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:MI:SS' ) AS CurrentSystemTimestamp,
TO_CHAR ( SYSDATE, 'SSSSS' ) AS CurrentSeconds,
TO_CHAR ( SYSDATE, 'RM' ) AS CurrentMonthRomanNumerals,
TO_CHAR ( SYSDATE, 'MM' ) AS CurrentMonth,
TO_CHAR ( SYSDATE, 'MONTH' ) AS CurrentMonthName,
TO_CHAR ( SYSDATE, 'DD' ) AS CurrentDay,
TO_CHAR ( SYSDATE, 'DAY' ) AS CurrentDayName,
TO_CHAR ( SYSDATE, 'YYYY' ) AS CurrentYear,
TO_CHAR ( SYSDATE, 'YEAR' ) AS CurrentYearName,
TO_CHAR ( SYSDATE, 'Q' ) AS CurrentQuarter,
TO_CHAR ( SYSDATE, 'WW' ) AS WeekNumber
FROM Dual
Explanation of Key Format Elements:
- YYYY-MM-DD HH:MI:SS – Displays the full timestamp in standard date and 12-hour time format.
- SSSSS – Seconds past midnight.
- RM – Roman numeral representation of the month.
- MM – Two-digit numeric month.
- MONTH – Full month name.
- DD – Two-digit day of the month.
- DAY – Full day name.
- YEAR – Full year in words (e.g., "TWENTY TWENTY-FIVE").
- Q – Quarter of the year (1–4).
- WW – Week number of the year.
To know more about Dual Table, check NetSuite Admin Tip | SuiteQL: Understanding the Dummy Table article
The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect, or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
How are you using TO_CHAR in SuiteQL in your business? Share them in the NetSuite Admin Corner