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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

SQL SYSDATE tips

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;

SYSDATE-(SYSDATE-3)
-------------------
                  3

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:

select
   (sysdate-(sysdate-3.111))*1440
from
   dual;

(SYSDATE-(SYSDATE-3.111))*1440
------------------------------
                    4479.83333

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:

select
   round(to_number(sysdate-(sysdate-3.111))*1440)
from
   dual;

ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)
----------------------------------------------
                                          4480

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 and SQL SYSDATE

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.

SQL> SELECT SYSDATE FROM dual;

SYSDATE
---------
23-JAN-05

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.

SQL> SELECT
2 SYSDATE Today,
3 SYSDATE - 1 Yesterday,
4 SYSDATE + 1 Tomorrow
5 FROM
6 dual;

TODAY YESTERDAY TOMORROW
--------- --------- ---------
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 dba poster
 

 
 
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.