Date Functions

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 12.  Understanding Dates and Times


Date functions are available in SQL depending on the options with each specific implementation. Date functions, similar to character string functions, are used to manipulate the representation of date and time data. Available date functions are often used to format the output of dates and time in an appealing format, compare date values with one another, compute intervals between dates, and so on.

The Current Date

You may have already raised the question: How do I get the current date from the database? The need to retrieve the current date from the database may originate from several situations, but the current date is normally returned either to compare to a stored date or to return the value of the current date as some sort of timestamp.

graphics/newterm_icon.gif

The current date is ultimately stored on the host computer for the database, and is called the system date. The database, which interfaces with the appropriate operating system, has the capability to retrieve the system date for its own purpose or to resolve database requests , such as queries.

Take a look at a couple of methods of attaining the system date based on commands from two different implementations .

Sybase uses a function called GETDATE() to return the system date. This function is used in a query as follows . The output is what would return if today's current date were New Year's Eve for 1999.

 graphics/mysql_icon.gif graphics/oracle_icon.gif graphics/input_icon.gif  SELECT GETDATE()  graphics/output_icon.gif Dec 31, 1999 
graphics/note_icon.gif

Most options discussed in this book for Sybase's and Microsoft's implementations are applicable to both implementations because both use SQL Server for their database server. Both implementations also use an extension to standard SQL known as Transact-SQL.


Oracle uses what is calls a pseudocolumn, SYSDATE, to retrieve the current date. SYSDATE acts as any other column in a table and can be selected from any table in the database, although it is not actually part of the table's definition.

To return the system date in Oracle, the following statement returns the output if today were New Year's Eve before 2002:

 graphics/mysql_icon.gif graphics/input_icon.gif  SELECT SYSDATE FROM TABLE_NAME  graphics/output_icon.gif 31-DEC-01 

Time Zones

The use of time zones may be a factor when dealing with date and time information. For instance, a time of 6:00 p.m. in central United States does not equate to the same time in Australia, although the actual point in time is the same. Some of us who live within the daylight saving time zone are used to adjusting our clocks twice a year. If time zones are considerations when maintaining data in your case, you may find it necessary to consider time zones and perform time conversions, if available with your SQL implementation.

The following are some common time zones and their abbreviations:

Abbreviation

Definition

AST, ADT

Atlantic standard, daylight time

BST, BDT

Bering standard, daylight time

CST, CDT

Central standard, daylight time

EST, EDT

Eastern standard, daylight time

GMT

Greenwich mean time

HST, HDT

Alaska/Hawaii standard, daylight time

MST, MDT

Mountain standard, daylight time

NST

Newfoundland standard, daylight time

PST, PDT

Pacific standard, daylight time

YST, YDT

Yukon standard, daylight time

The following table shows examples of time zone differences based on a given time:

Time Zone

Time

AST

June 12th, 2002 at 1:15 PM

BST

June 12th, 2002 at 6:15 AM

CST

June 12th, 2002 at 11:15 AM

EST

June 12th, 2002 at 12:15 PM

GMT

June 12th, 2002 at 5:15 PM

HST

June 12th, 2002 at 7:15 AM

MST

June 12th, 2002 at 10:15 AM

NST

June 12th, 2002 at 1:45 PM

PST

June 12th, 2002 at 9:15 AM

YST

June 12th, 2002 at 8:15 AM

graphics/note_icon.gif

Some implementations have functions that allow you to deal with different time zones. However, not all implementations may support the use of time zones. Be sure to verify the use of time zones in your particular implementation, as well as the need in the case of your database.


Adding Time to Dates

Days, months, and other parts of time can be added to dates for the purpose of comparing dates to one another, or to provide more specific conditions in the WHERE clause of a query.

Intervals can be used to add periods of time to a DATETIME value. As defined by the standard, intervals are used to manipulate the value of a DATETIME value, as in the following examples:

 graphics/mysql_icon.gif graphics/input_icon.gif  DATE '1999-12-31' + INTERVAL '1' DAY  graphics/output_icon.gif '2000-01-01' graphics/input_icon.gif  DATE '1999-12-31' + INTERVAL '1' MONTH  graphics/output_icon.gif '2000-01-31' 

The following is an example using the SQL Server function DATEADD:

 graphics/input_icon.gif  SELECT DATEADD(MONTH, 1, DATE_HIRE)   FROM EMPLOYEE_PAY_TBL;  graphics/output_icon.gif DATE_HIRE ADD_MONTH --------- --------- 23-MAY-89 23-JUN-89 17-JUN-90 17-JUL-90 14-AUG-94 14-SEP-94 28-JUN-97 28-JUL-97 22-JUL-96 22-AUG-96 14-JAN-91 14-FEB-91 6 rows affected. 

The following example uses the Oracle function ADD_MONTHS:

 graphics/mysql_icon.gif graphics/input_icon.gif  SELECT DATE_HIRE, ADD_MONTHS(DATE_HIRE,1)   FROM EMPLOYEE_PAY_TBL;  graphics/output_icon.gif DATE_HIRE ADD_MONTH --------- --------- 23-MAY-89 23-JUN-89 17-JUN-90 17-JUL-90 14-AUG-94 14-SEP-94 28-JUN-97 28-JUL-97 22-JUL-96 22-AUG-96 14-JAN-91 14-FEB-91 6 rows selected. 

To add one day to a date in Oracle, use the following:

 graphics/input_icon.gif  SELECT DATE_HIRE, DATE_HIRE + 1   FROM EMPLOYEE_PAY_TBL   WHERE EMP_ID = '311549902';  graphics/output_icon.gif DATE_HIRE DATE_HIRE --------- --------- 23-MAY-89 24-MAY-89 1 row selected. 

Notice that these examples in SQL Server and Oracle, although they differ syntactically from the ANSI examples, derive their results based on the same concept as described by the SQL standard.

Comparing Dates and Time Periods

OVERLAPS is a powerful standard SQL conditional operator for DATETIME values. The OVERLAPS operator is used to compare two timeframes and return the Boolean value TRUE or FALSE, depending on whether the two timeframes overlap. The following comparison returns the value TRUE:

 (TIME '01:00:00' , TIME '05:59:00')  OVERLAPS (TIME '05:00:00' , TIME '07:00:00') 

The following comparison returns the value FALSE:

 (TIME '01:00:00' , TIME '05:59:00')  OVERLAPS (TIME '06:00:00 , TIME '07:00:00') 

Miscellaneous Date Functions

The following list shows some powerful date functions that exist in the implementations for SQL Server, Oracle, and MySQL.

SQL Server

 

DATEPART

Returns the integer value of a DATEPART for a date

DATENAME

Returns the text value of a DATEPART for a date

GETDATE()

Returns the system date

DATEDIFF

Returns the difference between two dates for specified date parts, such as days, minutes, and seconds

Oracle

 

NEXT_DAY

Returns the next day of the week as specified (for example, FRIDAY) since a given date

MONTHS_BETWEEN

Returns the number of months between two given dates

MySQL

 

DAYNAME(date)

Displays day of week

DAYOFMONTH(date)

Displays day of month

DAYOFWEEK(date)

Displays day of week

DAYOFYEAR(date)

Displays day of year


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net