Published on December 10, 2020.

Working with dates and times can be tricky, regardless of what programming language you're using. And it can be all too easy to make false assumptions about how dates and times work. (If you don't believe me, take a look at "Falsehoods programmers believe about time" and "More falsehoods programmers believe about time; “wisdom of the crowd” edition" by Noah Sussman.)

Thankfully, NetSuite provides access to several Oracle functions that make working with dates and times in SuiteQL a little easier. In this post, I'll cover several of the functions that I've found to be most helpful.

The DUAL Table

Before getting started, I want to mention that all of the queries in this post are being made against the DUAL table. If you've worked with Oracle databases in the past will likely recognize the DUAL table. If not, here's a brief overview of what it is.

The DUAL table is an odd table. It only ever has a single row in it, and a single column named "Dummy." Run this query to see the DUAL table.

SELECT * FROM DUAL

You're probably wondering why the table exists, and that's a good question. In order to execute a SELECT statement, you have to specify a FROM clause, and as a result, at least one table must be specified.

But what if you want to run a query that doesn't involve data from a table?

You could do something like this:

SELECT 1 + 1 AS Two FROM Employee WHERE ROWNUM = 1

But that's terribly inefficient. And this is where the DUAL table comes in handy. Here's the same query be run against the DUAL table.

SELECT 1 + 1 AS Two FROM Dual

So now you're probably wondering why you'd ever want to run a query that doesn't involve data from a real table. The queries in this blog post are a few examples. I'm using DUAL to show examples of Oracle's date/time-related functions.

For more information on the DUAL table, check out this Wikipedia article.

Current Date and Time

There are several functions that you can use to get the current date and time, and the best function will depend on how you want to define "current." For example, the current time on the server that your NetSuite account is hosted on might be different from a user's time, because the server and the user might be in different timezones.

The two functions that I use are SYSDATE and CURRENT_DATE. SYSDATE returns the current date and time based on the server, and CURRENT_DATE returns the current date and time based on the user (and specifically based on the user's session). Here's a query that uses the two functions.

SELECT
	SYSDATE AS CurrentTimeServer,
	CURRENT_DATE AS CurrentTimeUser
FROM
	Dual

That query returns the the current timestamps based on the server and the user. However, if you look at the results of that query, you'll probably immediately notice that only the date portion of the timestamp is returned. This is something that SuiteQL seems to do by default.

Datetime Format Models

If you want to see the full date and time portions of a timestamp, you can use the TO_CHAR function, along with a datetime format model. Here's an example.

SELECT
	TO_CHAR ( SYSDATE, 'DS TS' ) AS CurrentTimeServer,
	TO_CHAR ( CURRENT_DATE, 'DS TS' ) AS CurrentTimeUser,
FROM
	Dual

Notice the use of the TO_CHAR function, and the "DS TS" format model. The "DS" portion of the model returns the date portion of the timestamp in what is known as a short date format, and the "TS" portion returns the time portion in a short time format. The values returned will end up looking like: 12/9/2020 11:01:10 PM

There are several datetime format elements that you can use to specify the format that you want a timestamp returned in. Take a look at this query, which shows a number of those elements.

SELECT
	TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:MI:SS' ) AS CurrentSystemTimestamp,
	TO_CHAR ( SYSDATE, 'SSSSS' ) AS SecondsSinceMidnight,	
	TO_CHAR ( SYSDATE, 'RM' ) AS MonthRoman,
	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

Note that I'm using SYSDATE in that query. Again, if you want the results to be based on the user's time, you would want to use CURRENT_DATE instead.

Also note that you can mix and match the format elements to specify the specific format that you want. For example...

SELECT
	TO_CHAR ( SYSDATE, 'DAY, MONTH DD, YYYY' ) AS MyDate
FROM
	Dual

That query would return a value such as: WEDNESDAY, DECEMBER 09, 2020

Creating Date Values

To create a date value, you can use the TO_DATE function. It converts a character value into a DATE value that Oracle can work with.

To use the TO_DATE function, you pass it the character value of the date, along with a datetime format model. Here's an example.

SELECT
	TO_DATE( '2020-01-01', 'YYYY-MM-DD' ) AS AdHocDate
FROM
	Dual

To create a full timestamp value, you would simply change the datetime format model so that it includes the time-related elements. Here's another example.

	
SELECT
	TO_CHAR ( TO_DATE( '2020-01-01 09:10:11', 'YYYY-MM-DD HH:MI:SS' ), 'YYYY-MM-DD HH:MI:SS' ) AS AdHocTimestamp,
FROM
	Dual

Note that I'm wrapping the result of the TO_DATE function in TO_CHAR, so that you can see that the resulting value includes the time portion.

Date Arithmetic

Adding and subtracting from dates is surprisingly easy. You simply add or subtract a specific number of days from the value. Here's an example.

SELECT
	( SYSDATE - 1 ) AS PreviousDay,
	( SYSDATE + 1 ) AS NextDay
FROM
	Dual

There are a few functions that you can use as well, which might come be helpful. They include NEXT_DAY, ADD_MONTHS, and LAST_DAY.

NEXT_DAY returns the first weekday, specified by the day's name, after the date. As an example, to get the date of the next Saturday after the current date, you'd use something like this.

SELECT
	NEXT_DAY( SYSDATE, 'SATURDAY') AS Saturday,
FROM
	Dual

The ADD_MONTHS function makes it easy to add or subtract months from a date. Here's an example.

SELECT
	ADD_MONTHS( SYSDATE, -1 ) AS DatePreviousMonth,
	ADD_MONTHS( SYSDATE, 1 ) AS DateNextMonth,
	LAST_DAY( SYSDATE ) AS LastDayOfMonth,
FROM
	Dual

LAST_DAY returns the last day of the month, based on the specified date. Here's an example of it being used to get the last day of the current month.

	
SELECT
	LAST_DAY( SYSDATE ) AS LastDayOfMonth,
FROM
	Dual

Time Arithmetic

I've shown several ways that you can add and subtract days and months from a date. But what about adding and subtracting time?

To do so, you simply add or subtract a fraction of a day. For example, to add 10 minutes to a date, you'd add 10 / 1440, where 1440 is the number of minutes in a day. Similarly, to add 10 seconds, you'd add 10 / 86400, where 86400 is the number of seconds in a day. Here's an example.

SELECT
	TO_CHAR ( SYSDATE, 'TS' ) AS NOW,
	TO_CHAR ( SYSDATE + ( 10 / 1440 ), 'TS' ) AS SOON,
	TO_CHAR ( SYSDATE + ( 10 / 86400 ), 'TS' ) AS SOONER
FROM
	Dual

Time Zones and Offsets

Before wrapping up, let's take a look at a few other functions that you might find helpful. These involve time zones and offsets.

TZ_OFFSET can be used to get a time zone offset based on a specified value and the current date. An example returned value is -05:00.

SESSIONTIMEZONE returns the time zone of the user based on their session. For example, I'm in Virginia at the moment, so the value returned is "America/New_York."

Here's a query showing those functions in use.

SELECT
	TZ_OFFSET( 'US/Eastern' ) AS Offset1,
	TZ_OFFSET( SESSIONTIMEZONE ) AS Offset2,
	SESSIONTIMEZONE AS SessionTimeZone,	
FROM
	Dual

Wrapping Up

That's probably way more than you ever wanted to know about working with dates and times in SuiteQL. The big takeaway is that NetSuite provides us with a number of helpful functions that make working with dates and times easier. I think the trick to working dates and times is mixing and matching the functions as needed.

If you need help with SuiteQL, please feel free to contact me. I'm booked up through the end of 2020, but will be providing one-hour support calls starting in January 2021.