Date Handling
(Page 1 of 4 )
SQL can handle many date calculations. This four-part series will show you how to do date calculations against your database and get reports based on specific periods of time. This article, the first in the series, is excerpted from chapter four of the book
SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
Hacks 19–23
SQL is capable of handling just about any date calculation. The hacks in this chapter show how to get dates into your database, and how to get weekly, monthly, and quarterly reports out with a minimum of human intervention.
In many of the hacks described here, the reports are generated using the current date; however, it is usually a simple matter to use a user-specified parameter instead [Hack #58].
There are inconsistencies among the main database vendors regarding dates. For most of the hacks in this chapter, we used MySQL as the base example and we’ve shown the variations for SQL Server, Access, Oracle, and PostgreSQL.
You should be aware that the database system might be running on a system having a different time zone than the system your applications run on (perhaps your web server is in New York and your database server is in Chicago). To minimize clock and time zone discrepancies, you should useCURRENT_TIMESTAMPto generate times whenever possible.
HACK #19 Convert Strings to Dates
The SQL standard includes a complete set of rules which govern how dates should be represented and manipulated. Each vendor implementation of SQL has a variation of these rules.
The SQL standard has aDATEtype for days and aTIMESTAMPtype to represent a date and time. Examples of literals areDATE '2006-05-20'andTIMESTAMP '2006-06-18 10:09:05'. The ISO format used in both examples (the year followed by the month followed by the day) has the advantage of sorting correctly even when it’s represented as a string data type. It is also visibly different from both the American convention that puts the month first, and the European style that puts the day first.
Oracle, PostgreSQL, and MySQL adhere to the SQL standard for representing dates and timestamps, but Microsoft’s SQL Server and Access use a slightly different approach. SQL Server and Access will accept a date literal such as'2006-06-08', but they cannot handle theDATEprefix.
TheDATEtype does not exist in SQL Server; you should use theDATETIMEtype to represent both a date and a moment in time. SQL Server uses the term TIMESTAMP for an entirely different purpose.
Next: Convert Your Dates >>
More SQL Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.
|
|
