Home arrow SQL arrow Page 4 - SQL Date Handling and Data Trends
SQL

SQL Date Handling and Data Trends


In this second part of a four-part series explaining how SQL handles date calculations, you will learn how to uncover trends in your data. This article 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.

Author Info:
By: O'Reilly Media
Rating: 5 stars5 stars5 stars5 stars5 stars / 6
December 13, 2007
TABLE OF CONTENTS:
  1. · SQL Date Handling and Data Trends
  2. · Turning the Dates into Integers
  3. · Modular Arithmetic
  4. · Changes for SQL Server, Access, and Oracle

print this article
SEARCH DEVARTICLES

SQL Date Handling and Data Trends - Changes for SQL Server, Access, and Oracle
(Page 4 of 4 )

SQL Server

Here’s how to create the view that represents dates as integers:

  CREATE VIEW webalizer2 AS
    SELECT CONVERT(INT,whn-'2001-01-01') whn, pages
      FROM webalizer

TheSELECTstatements shown earlier will run unmodified.

Access

In Access, you can useInt(whn -#2001-01-01#)to extract the number of days since January 1, 2001:

  SELECT Int(whn - #2001-01-01#), pages
    FROM webalizer

Also,MOD is an infix operator used in place of%:

  SELECT whn MOD 7, AVG(pages)
    FROM webalizer2 GROUP BY whn MOD 7;

Oracle

Here’s how to create the view that represents dates as integers:

  CREATE VIEW webalizer2 AS
    SELECT whn-DATE '2001-01-01' whn, pages
      FROM webalizer;

In Oracle, the module function isMOD, so you’d need to use that rather thanwhn%7:

  SELECT MOD(whn,7), AVG(pages)
    FROM webalizer2 GROUP BY MOD(whn,7);

Please check back next week for the continuation of this article.


DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

blog comments powered by Disqus
SQL ARTICLES

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials