Oracle SYSDATE Function Guide, FAQ, & Examples

Oracle SYSDATE is one of the most popular functions in Oracle. Learn what it does and how to SELECT and INSERT it in this article.

Purpose of the Oracle SYSDATE Function

The Oracle SYSDATE function is used to show the current date and time of the operating system that the database runs on.

It’s a quick and easy SQL function for finding the current date, or current date and time.

 

Syntax

The syntax of the SYSDATE function is simple:

SYSDATE

There are no parameters to provide – just enter the word SYSDATE.

The return type is a DATE. However, the Oracle DATE datatype actually includes a time component. This is something that is often forgotten.

This means the SYSDATE includes the time as well as the date. The time can be displayed in a few ways and depends on the NLS_DATE_FORMAT parameter. See the Examples further down for more information.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

Oracle SYSDATE Format

The format shown by SYSDATE is dependent on the database parameter called NLS_DATE_FORMAT.

By default, it is shown as:

DD-MON-RR

That means two digits for the day, a dash, then the abbreviated month name, then another dash, and then two digits for the year.

So, the date of 25th September 2022 will be shown as 25-SEP-22.

This can be changed in several ways:

• Updating the NLS_DATE_FORMAT parameter on your database or in your session
• Using a function to change the way that SYSDATE is displayed (TO_CHAR)

 

What Does SYSDATE – 1 Show?

You can perform arithmetic on DATE values, and SYSDATE is no different.

If you subtract 1 from SYSDATE by using a query like this:

SELECT SYSDATE - 1 FROM dual;

You will get the date that is one day before the current date.

So, any addition or subtraction performed on DATE values is interpreted in whole days.

 

How Can I Insert SYSDATE In Oracle?

To insert a SYSDATE value in Oracle, you can just insert the SYSDATE function into a DATE column.

This is the ideal method because both the SYSDATE and the DATE column are in a date datatype. There’s no need to convert when inserting into the table.

The INSERT statement would look like this:

INSERT INTO your table (datecolumn) VALUES (SYSDATE);

If you want to see the SYSDATE in a different format, then this should be done when you SELECT it.

 

Examples of the SYSDATE Function

Let’s take a look at some examples of the SYSDATE function.

Example 1 – Basic SYSDATE

This example shows a basic use of the SYSDATE function.

SELECT SYSDATE
FROM dual;

SYSDATE

01-SEP-22

This shows the current date according to the databases’ operating system (which is my computer).

 

Example 2 – SYSDATE – 1 and SYSDATE + 1

This example shows what happens when we add and subtract 1 from SYSDATE.

SELECT
SYSDATE,
SYSDATE - 1 AS minus1,
SYSDATE + 1 AS plus1
FROM dual;

SYSDATE	MINUS1	PLUS1
01-SEP-22	31-AUG-22	02-SEP-22

It shows a value of today, yesterday, and tomorrow.

 

Example 3 – Elapsed Time

We can expand on the previous example to show elapsed time, or the differences between two dates.

SELECT
SYSDATE,
(SYSDATE - TO_DATE('01-JAN-22')) AS days_diff,
(SYSDATE - TO_DATE('01-JAN-22')) * 24 AS hrs_diff,
(SYSDATE - TO_DATE('01-JAN-22')) * 24 * 60 AS mins_diff,
(SYSDATE - TO_DATE('01-JAN-22')) * 24 * 60 * 60 AS secs_diff
FROM dual;

SYSDATE	DAYS_DIFF	HRS_DIFF	MINS_DIFF	SECS_DIFF
01-SEP-22	273.3911343	6561.387222	393683.2333	23620994

This query shows the current date, and the difference between the current date and Jan 1st 2022 in days, hours, minutes, and seconds.

We have multiplied the day difference by 24 to convert to hours, then by 60 to convert to minutes, and then by 60 again to convert to seconds.

 

Example 4 – Formatting SYSDATE with TO_CHAR

This example shows how we can format the output of SYSDATE to show the time component as well.

SELECT
TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) AS now_date_time
FROM dual;

NOW_DATE_TIME

01-SEP-22 08:02:44

It shows the current date and time.

 

Example 5 – Changing NLS_DATE_FORMAT

Another way we can change the format of SYSDATE is to change the NLS_DATE_FORMAT parameter.

First, let’s see how SYSDATE is shown.

SELECT SYSDATE FROM dual;

SYSDATE

01-SEP-22

We can see it’s only showing the date and not the time.

We can view the parameter responsible for this formatting, NLS_DATE_FORMAT, using this query:

SELECT *
FROM nls_parameters
WHERE parameter = 'NLS_DATE_FORMAT';

VALUE

DD-MON-RR

The format that is used is DD-MON-RR as explained earlier.

Let’s change this to include the time component. Rather than updating this table, we run a command to alter the session. We’ll just change it for our session, not for the entire database.

ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS';

The parameter has now changed to show the date in the same way, but then show the 24 hour format of the time.

Let’s check that parameter table again.

SELECT value
FROM nls_parameters
WHERE parameter = 'NLS_DATE_FORMAT';

VALUE

DD-MON-RR HH24:MI:SS

The format has been updated to the value we set it to.

Now, let’s run our SYSDATE query again.

SELECT SYSDATE FROM dual;

SYSDATE

01-SEP-22 09:16:54

We can see it shows the same date but now includes the time.

This is because we changed our date format using that ALTER SESSION command.

 

Example 6 – Inserting a SYSDATE

To insert SYSDATE into a table, we just need to ensure that the column is a DATE data type.

CREATE TABLE sysdate_test (
result_num NUMBER(10),
sysdate_val DATE
);
 
INSERT INTO sysdate_test(result_num, sysdate_val)
VALUES (1, SYSDATE);
 
SELECT result_num, sysdate_val
FROM sysdate_test;

SYSDATE

01-SEP-22 09:27:07

You can see that the date value has been inserted into the table successfully.

 

Similar Functions

Some functions which are similar to the SYSDATE function are:

• CURRENT_DATE: This shows the date and time in the session time zone.
• SYSTIMESTAMP: This shows the date and time of the database, and returns a TIMESTAMP WITH TIME ZONE.
• CURRENT_TIMESTAMP: This shows the date and time of the session, and returns a TIMESTAMP WITH TIME ZONE.
• LOCALTIMESTAMP: This shows the date and time of the session, and returns a TIMESTAMP.

More information on these functions can be found in my guide to Oracle date functions.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: