Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.