Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle Tips by Burleson Consulting
Don Burleson

Question:  I need to understand how to add a data into my Oracle database with the SQL SYSDATE function.  Can you show an example of using SYSDATE with SQL?

Answer:  One of the confounding problems with Oracle SQL is the SYSDATE function. 

The purpose of SYSDATE is simply to return the current date and time associated with the OS on which the database in question resides.  The returned value of SYSDATE is of the datatype: DATE.  The format of the value returned by SYSDATE is determined by the nls_date_format initialization parameter.

- See here for the nls_date_format parameters for SYSDATE display

- See here for hate math manipulation with SQL SYSDATE

When using distributed SQL statements, the SYSDATE function returns the date and time that are set for the operating system of the local database.  SYSDATE cannot be used in the condition statement of a CHECK constraint.

Oracle SYSDATE functions can be used to convert into elapsed timeand place the SYSDATE value inside an Oracle table.

SQL> select sysdate-(sysdate-3) from dual;


Here we see that elapsed times are expressed in days.  Hence, we can use easy conversion functions to convert this to hours or minutes.  However, when the minutes are not a whole number, we have the problem of trailing decimal places:



Of course, we can overcome this with the ROUND function with Oracle SYSDATE, remembering that we must first convert the DATE datatype to a NUMBER type:



We can use these Oracle SYSDATE functions to convert an elapsed time into rounded elapsed minutes, and place the value inside an Oracle table.  In this example, we have a Oracle logoff system-level trigger that computes the elapsed session time and places it inside a Oracle STATSPACK user_log extension table:


Dates are stored in the database as large numbers. The actual size of the data number is dependent on the operating system supporting the database. When a date is requested, it is returned in a human readable form.

When date values are compared in the where clause, the format of the date must match the format that the database is using or the comparison will fail. Alternately, if you are using another format, then you must tell the database how your date is formatted. The default format that the Oracle database uses is: DD-Mon-YY. This is how SQL*Plus will show you the data, when requested. So, how do I reformat the date returned? We will cover date functions in single row functions in Chapter 2.

To get the current date, you select from a function called SYSDATE. SYSDATE returns the current date from the server operating system supporting the database.



The dual table is a pseudo-table that allows you to execute functions that require selecting from a table.

Lastly, because a date is stored in the database as a number, you can perform date math.

2 SYSDATE Today,
3 SYSDATE - 1 Yesterday,
4 SYSDATE + 1 Tomorrow
6 dual;

--------- --------- ---------
23-JAN-05 22-JAN-05 24-JAN-05

As you can see, the standard unit in date math is one day. When you add time to the date with SQL updates, you do it in fractions of a day.

1 Day 1 1 1
1 Hour 1/24 1/24 0.0417
1 Min 1/(24x60) 1/1440 .000694
1 Sec 1/(24x60x60) 1/86400 .000011574

The notation in the second column is most commonly used, because it is so much easier to read. Five minutes is 5/(24x60), much easier than 5/1440 or .00347. When we get to date functions in Chapter 2, you will see that there are functions to do date math by months, weeks and so forth.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.