MS Access DatePart Function examples

Examples of MS Access DatePart function

The date format in the examples below is determined by the Short date format that was set in Control Panel's Regional Options (dd-MMM-yyyy in this case).

For detailed description of DatePart function, refer to this page.

Expression	Interval	Description	Date to be evaluated	Optional Parameters	Returns
`DatePart("m",[OrderDate])`	m	Month of year	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	7
`DatePart("d",[OrderDate])`	d	Date of month	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	4
`DatePart("yyyy",[OrderDate])`	yyyy	Year (four-digit number)	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	1996
`DatePart("q",[OrderDate])`	q	Quarter of year	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	3
`DatePart("y",[OrderDate])`	y	Day of year	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	186
`DatePart("w",[OrderDate])`	w	Weekday	04-Jul-1996 3:37:24 PM	firstdayofweek: 1 - default value for Sunday. No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	5
`DatePart("w",[OrderDate], 2)`	w	Weekday	04-Jul-1996 3:37:24 PM	firstdayofweek: 2 - Monday firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	4
`DatePart("ww",[OrderDate])`	ww	Week of year	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	27
`DatePart("ww",[OrderDate], 1, 3)`	ww	Week of year	04-Jul-1996 3:37:24 PM	firstdayofweek: 1 - Sunday firstweekofyear: 3 - first full week of the year.	26
`DatePart("h",[OrderDate])`	h	Hour	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	15
`DatePart("n",[OrderDate])`	n	Minute	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	37
`DatePart("s",[OrderDate])`	s	Second	04-Jul-1996 3:37:24 PM	firstdayofweek: default value (Sunday). No need to specify. firstweekofyear: default value (the week in which January 1 occurs). No need to specify.	24

More examples:

DatePart function can be used in SQL queries SELECT, WHERE, GROUP BY, HAVING, SORT BY...

#1: Get the week of year for a literal date 22-Oct-2003.

SELECT datepart('ww', #22-Oct-2003#)

Note: default value of 1 (the week in which January 1 occurs) is used as the firstweekofyear.

#2: Retrieve all the orders that were placed on Monday.

SELECT OrderID, OrderDate
FROM Orders
WHERE datepart('w', OrderDate) = 2;

Note: default value of 1 (Sunday) is used as the firstdayofweek.

#3: Get the number of orders for each quarter of each year.

SELECT datepart('yyyy', OrderDate) as Year, 
       datepart('q', OrderDate) as Quarter, 
       count(OrderID) as [Order Count]
FROM Orders
GROUP BY OrderDate

Other Recent Articles from the MS Access category:

1.	Examples of MS Access DateDiff function used in query and VBA code
2.	MS Access DateDiff function
3.	How to find out your computer name and username by VBA
4.	MS Access DatePart function
5.	Examples of MS Access DateAdd function
6.	MS Access DateAdd function
7.	IIF function basics - the CASE statement of MS Access
8.	MS Access Date Expression
9.	Solved: MS Access error "The text is too long to be edited"
10.	Create MS Access Combo Box essential properties by VBA code
11.	Create MS Access Combo Box essential properties manually
12.	How to do text search in MS Access programmatically
13.	Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
14.	How to easily get a list of field names in MS Access
15.	How to count distinct records in MS Access
16.	How to do transaction based processing in MS Access
17.	How to open a document (local/network file or web page) from MS Access
18.	How to use ADOX to create unique composite index - the VBA approach
19.	How to do cross-table update queries in MS Access - the right way
20.	Three efficient ways to get the number of records by using VBA
21.	How to create a composite unique index (not as a primary key) in MS Access
22.	Use VBA to get the correct number of records in a Recordset object
23.	Disable Access Prompt when a record is changed, table deleted, or action queries run
24.	How to hide and unhide a MS Access object
25.	How to return multiple values from a VBA function (Part 3)
26.	How to return multiple values from a VBA function (Part 2)
27.	How to return multiple values from a VBA function (Part 1)
28.	Three ways to programmatically duplicate a table in MS Access by VBA
29.	Create a DLL by CSharp or VB.Net for VBA
30.	How to correctly reference and call a DLL
31.	How to register a C# or VB.Net DLL
32.	Email address validation by Regular Expressions using VBA
33.	Fix MS Access error: Query must have at least one destination field
34.	How to unselect radio buttons in MS Access after it has been selected
35.	How to Change Query Timeout Value for MS Access SQL Queries
36.	What is Northwind Traders database

	Custom Search