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
Copyright© GeeksEngine.com
| Other Recent Articles from the MS Access category:
|