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


  
Click to buy this book from Amazon
 

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

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: