AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com 


  
 

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

How to use the datepart() and format() functions to extract quarters  from a date field

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


 

Use the datepart() and format() functions to extract quarters  from a date field

Calculate order totals by quarter for a specific year

Discussion (with datepart):

The business goal in this example is to create a quarterly sales report for the year 2009.  To achieve this task, we need to use two date functions.  First, we will use the year() function to extract the year from the orderdate field and use this expression in the WHERE clause with an equality predicate "=".  This way, we make certain that our resulting recordset contains orders only for the year 2009.  Then, we can use the datepart() function to group by our order totals by quarter.  Of course, we could use the format function to extract quarters, which is demonstrated in the second part of this example. 

Code:

SELECT datePart("q",[OrderDate])  AS Quarter, sum(unitprice*quantity) AS OrderTotal

FROM Qry_Invoices

WHERE Year(orderdate) = 2009

GROUP BY DatePart("q",[OrderDate])  

Result:

 

Discussion (with format):

In this second part of the same example, we use the format() function to extract quarters from a date field.  The results will be identical with those of the datepart() function. 

Code:

SELECT format(OrderDate, "q") AS Quarter, sum(unitprice*quantity) AS OrderTotal

FROM Qry_Invoices

WHERE Year(orderdate) = 2009

GROUP BY format(OrderDate, "q") 

Result: