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:
SYSDATEThere 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.
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!