Oracle SYSDATE is one of the most popular functions in Oracle. Learn what it does, 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.Oracle SYSDATE

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



The syntax of the SYSDATE function is simple:


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.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Oracle SYSDATE Format

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

By default, it is shown as:


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

So, the date of 25th September 2017 will be shown as 25-SEP-17.

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:


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.

FROM dual;

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.

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

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.

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

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

We have multiplied the day difference by 24 to convert to hours, and 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.

FROM dual;

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.


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:

FROM nls_parameters
WHERE parameter = 'NLS_DATE_FORMAT';

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.


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';

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

Now, let’s run our SYSDATE query again.

01-OCT-17 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;
01-OCT-17 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!

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Get Your SQL Cheat Sheets Now: