databasedev.co.uk - database solutions and downloads for microsoft access

The DatePart Function

In Microsoft Access, we can use the DatePart function to evaluate a date and return a specific interval of time. For example, you might use DatePart to calculate the day of the week, the year value from the date, or the current hour.

Syntax

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The DatePart function syntax has the following named arguments:

Part	Description
interval	Required. String expression that is the interval of time you want to return.
date	Required. Variant (Date) value that you want to evaluate.
firstdayofweek	Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear	Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Settings

The interval argument has these settings:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

Ignoring the optional parts of the DatePart syntax, we use the interval as the time that we want to return from the function and the date is the date value that we want to evaluate.

Using the DatePart Function in a Microsoft Access Query

In the following example, we are going to use the DatePart function within a query.

In the scenario, the data that we will use is a list of sales of Movies. Each sale contains an Order Purchase Date. We will use the DatePart function to extract the Year from our date, which we can then use to group our records on in the query. Below we can see how the data appears in the Orders table:

As you can see we have a Purchase Order Date, that records when the sale of the item was made. If we want to analyse the data, we may want to see how many sales, and how much value was made in sales per year.

If we create our query, we will include the following fields in the design:

Using these fields will give the following initial results, that at the moment don't provide much information. They don't give us a count of how many sales or how much profit has been made:

What we can now do, is create an expression in the query design, that will provide us with the Year that the Order was made. We use the following expression to provide us with the value:

OrderDateYear: DatePart("yyyy",[dtePurchaseOrderDate])

Our query design now appears as follows, with the expression replacing the dtePurchaseOrderDate field in the query design:

Again, when the query is ran, the results are still not that meaningful. The only difference now is that we can view the year that the Order was made, as a result of using the DatePart function in the expression:

To allow us to view meaningful data to analyse, we should add some Grouping and Aggregate functions in our query design. The following shows the addition of the Totals Row in the query design. Using this, we then group the data on the expression that uses the DatePart function, and add a Count to the MovieID field, and a Sum to the OrderCost field:

The following results can then be viewed by running the query:

The results are now grouped by the Order Date Year that we have gained by using the DatePart function on our Order Date field.