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.

��