 |
|
Oracle Dates and SQL
|
Dates and SQL
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.
Using SQL Comparison Operators
A comparison operator evaluates two values and
returns a TRUE, FALSE or NULL. Comparison operates are used in the
WHERE clause to limit the number of returned rows.
Equals = WHERE first_name =
‘BILL’
Not Equals != WHERE
state != ‘FL’
<> WHERE state <> ‘FL’
^= WHERE state ^=
‘FL’
Less Than < WHERE pay <
min_wage
Greater Than > WHERE pay >
my_pay
Less Than or Equal <= WHERE pay <=
2000
Greater Than or Equal >= WHERE pay >= 100000
There are special comparison operators that are
used with multiple values.
Between…and… Validates that a value is
between the first and second values, inclusive. WHERE pay between
100000 and 150000
IN (…) Validates that a value is contained in
the list of values. WHERE state IN (‘FL’, ‘CO’, ‘UT’, ‘GA’)
LIKE Like matches a character pattern. There
are two special characters used to match characters. The percent % is
zero or more characters wildcard (like the OS * character). The
underscore _ is a single character wild card. WHERE name LIKE ‘sam%’
will return any match that starts with ‘sam’, including the word sam.
(sam, sammy, samatha, samer, etc). If you are looking to match one of
the special characters, you must include an escape character so that
the database treats it for what it is and not as a wildcard
character. For example, if I wanted to get all the rows where
process_name started with ora_ , I would have to escape the _
character. WHERE process_name LIKE ‘ora\_%’.
NOT The NOT operator simply negates the operator
following. NOT IN, NOT BETWEEN, NOT LIKE.
ANY, SOME, ALL These operators follow an =, !=,
<,>,<= or >= operator. They allow these normally single values
comparison operators to work with multiple values in a list or
returned by a subquery.
ANY Returns TRUE if any value in the list
satisfies the operator. The SOME operator is interchangeable with the
ANY operator. WHERE state = ANY (select author_state from AUTHOR)
ALL Returns TRUE only if all values in the list
satisfy the comparison. WHERE pay < ALL (100000, 150000, 200000).
pay of 80000 will return TRUE, but pay of 125000 will return FALSE.
EXIST The EXIST operator returns TRUE if a
subquery returns at least one row. Likewise, NOT EXIST returns TRUE
if the subquery does not return at least one row.
IS NULL Returns TRUE if the value is NULL. IS
NOT NULL returns TRUE if the value is not NULL. WHERE author_state
IS NULL.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
