DatePart
Synopsis
DatePart(interval,date[,firstdayofweek[,firstweekofyear]])
Arguments
| interval | A string expression code that is the interval of time you want to return. See below for code values. |
| date | Date expression you want to evaluate, specified as a quoted string. |
| firstdayofweek | Optional — Constant that specifies the day of the week. If not specified, Sunday is assumed. See below for values. |
| firstweekofyear | Optional — 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. See below for values. |
Description
You can use the DatePart function to evaluate a date and return a specific interval as an integer value. For example, you might use DatePart to calculate the day of the week or the number of days since the start of the year.
The interval argument can have the following values:
| Setting | Description |
|---|---|
| yyyy | Year date |
| q | Quarters since beginning of year |
| m | Month date; number of months since beginning of year |
| y | Day of Year; number of days since beginning of year. |
| d | Day date; number of days since beginning of month. |
| w | Weekday (day of the week, with Sunday counted as 1) |
| ww | Weeks since beginning of year |
| h | Hour (defaults to 1). |
| n | Minute (defaults to 0). |
| s | Second (defaults to 0). |
The firstdayofweek argument can have the following values:
| Constant | Value | Description |
|---|---|---|
| vbUseSystem | 0 | Use National Language Support (NLS) API setting. |
| vbSunday | 1 | Sunday |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
The firstweekofyear argument can have the following values:
| Constant | Value | Description |
|---|---|---|
| vbUseSystem | 0 | Use National Language Support (NLS) API setting. |
| vbFirstJan1 | 1 | Use the week in which January 1 occurs (default). |
| vbFirstFourDays | 2 | Use the first week that has at least four days in the new year. |
| vbFirstFullWeek | 3 | Use the first full week of the year. |
You must specify a firstdayofweek argument value in order to specify a firstweekofyear argument value. The firstdayofweek argument affects calculations that use the "w" and "ww" intervals.
If date is a date literal, the specified year becomes a permanent part of that date. However, if date is enclosed in quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date expression is evaluated. This makes it possible to write code that can be used in different years.
Examples
The following example takes a date and displays the corresponding interval counts:
NewDay = DatePart("d","30-Nov-2005")
NewWDay = DatePart("w","30-Nov-2005")
NewWeek = DatePart("ww","30-Nov-2005")
NewMonth = DatePart("m","30-Nov-2005")
NewQuarter = DatePart("q","30-Nov-2005")
NewYDay = DatePart("y","30-Nov-2005")
NewYear = DatePart("yyyy","30-Nov-2005")
NewHour = DatePart("h","30-Nov-2005")
NewMin = DatePart("n","30-Nov-2005")
NewSec = DatePart("s","30-Nov-2005")
Println NewDay
Println NewWDay
Println NewWeek
Println NewMonth
Println NewQuarter
Println NewYDay
Println NewYear
Println NewHour
Println NewMin
Println NewSecThe following example shows the effects of the firstdayofweek argument:
MyDay0 = DatePart("w","11/1/2005",vbUseSystem)
MyDay1 = DatePart("w","11/1/2005",vbSunday)
MyDay2 = DatePart("w","11/1/2005",vbMonday)
MyDay3 = DatePart("w","11/1/2005",vbTuesday)
MyDay4 = DatePart("w","11/1/2005",vbWednesday)
MyDay5 = DatePart("w","11/1/2005",vbThursday)
MyDay6 = DatePart("w","11/1/2005",vbFriday)
MyDay7 = DatePart("w","11/1/2005",vbSaturday)
Println "Day is: ",MyDay0," Week begins System Default"
Println "Day is: ",MyDay1," Week begins Sunday"
Println "Day is: ",MyDay2," Week begins Monday"
Println "Day is: ",MyDay3," Week begins Tuesday"
Println "Day is: ",MyDay4," Week begins Wednesday"
Println "Day is: ",MyDay5," Week begins Thursday"
Println "Day is: ",MyDay6," Week begins Friday"
Println "Day is: ",MyDay7," Week begins Saturday"Nov. 1, 2005 is a Tuesday. DatePart("w","11/1/2005",vbTuesday) returns 1.
The following example returns the week of the year count for February 29, 2008, based on different firstdayofweek and firstweekofyear argument values:
Println "Week is: ",DatePart("ww","2/29/2008",vbUseSystem,vbUseSystem)
Println "Week is: ",DatePart("ww","2/29/2008",vbThursday,vbUseSystem)
Println "Week is: ",DatePart("ww","2/29/2008",vbUseSystem,vbFirstJan1)
Println "Week is: ",DatePart("ww","2/29/2008",vbThursday,vbFirstJan1)
Println "Week is: ",DatePart("ww","2/29/2008",vbUseSystem,vbFirstFourDays)
Println "Week is: ",DatePart("ww","2/29/2008",vbThursday,vbFirstFourDays)
Println "Week is: ",DatePart("ww","2/29/2008",vbUseSystem,vbFirstFullWeek)
Println "Week is: ",DatePart("ww","2/29/2008",vbThursday,vbFirstFullWeek)Note that both firstweekofyear and firstdayofweek can affect the week of the year count.
