DATEPART

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

Returns the specified part of a given date (Variant / Integer).


intervalThe interval of time you want to add (String):
"yyyy","YYYY" = year
"q","Q" = quarter
"m","M" = month
"y","Y" = day of year
"d","D" = day
"w","W" = weekday
"ww","WW" = week
"h","H" = hour
"n","N" = minute
"s","S" = second
dateThe date you want to evaluate (Date).
firstdayofweek(Optional) A vbDayOfWeek constant that specifies the first day of the week:
0 = vbUseSystemDayofWeek
1 = vbSunday (default)
2 = vbMonday
3 = vbTuesday
4 = vbWednesday
5 = vbThursday
6 = vbFriday
7 = vbSaturday
firstweekofyear(Optional) A vbFirstWeekOfYear constant that specifies the first week of the year:
0 = vbUseSystem
1 = vbFirstJan1, the week containing Jan 1st (default)
2 = vbFirstFourDays, the first week containing 4 days
3 = vbFirstFullWeek, the first full week

REMARKS
* If "firstdayofweek" is left blank, then 1 is used (ie Sunday).
* If "firstweekofyear" is left blank, then the first week is assumed to be the week in which January 1 occurs.
* In the case of years , the calculation starts from year 0. but in the case of quarters, months, weeks, weeks of year, day of year it starts from the first day of the current year.
* The "firstdayofweek" is only relevant if the "interval" is either "w" or "ww".
* This function can be used to evaluate a date and return a specific interval of time.
* This function can be used to calculate the day of the week or the current hour.
* If the date is submitted as a string and does not specify a year (for example #20 July#) then the current year is automatically inserted.
* If you are calling this function from VB.Net then the following enumerations can be used: VisualBasic.DateInterval, VisualBasic.FirstDayOfWeek, VisualBasic.FirstWeekOfYear
* You can use the DATE function to return the current system date.
* You can use the DATEADD function to return the date with a specified time interval added.
* You can use the DATEDIFF function to return the number of a given time interval between two specified dates.
* You can use the DATESERIAL function to return the date given a year, month and day.
* You can use the DATEVALUE function to return the date given a string representation of a date.
* You can use the FORMAT function to return a text string of a date in a particular format.
* You can use the NOW function to return the current system date and time.
* The equivalent .NET function is Microsoft.VisualBasic.DateAndTime.DatePart
* link - learn.microsoft.com/en-us/office/troubleshoot/access/functions-return-wrong-week-number
* For the Microsoft documentation refer to learn.microsoft.com

Debug.Print DatePart("d", "01-01-2024")          '= 1  
Debug.Print DatePart("d", "10-01-2024") '= 10
Debug.Print DatePart("w", "10-01-2024") '= 4 this is the weekday of the date
Debug.Print DatePart("ww", "10-01-2024") '= 2
Debug.Print DatePart("m", "01-10-2024") '= 10
Debug.Print DatePart("y", VBA.Now()) '= 49
Debug.Print DatePart("h", "01-01-2024 12:00:00") '= 12
Debug.Print DatePart("n", "01-01-2024 00:12:00") '= 12
Debug.Print DatePart("s", "01-01-2024 00:00:30") '= 30

Dim dtDate As Date
dtDate = #7/20/2024 12:10:56 PM#
Debug.Print DatePart("yyyy", dtDate) '= 2024
Debug.Print DatePart("q", dtDate) '= 3
Debug.Print DatePart("m", dtDate) '= 7
Debug.Print DatePart("y", dtDate) '= 202
Debug.Print DatePart("d", dtDate) '= 20
Debug.Print DatePart("w", dtDate) '= 7
Debug.Print DatePart("ww", dtDate) '= 29
Debug.Print DatePart("h", dtDate) '= 12
Debug.Print DatePart("n", dtDate) '= 10

Dim iReturn As Integer
iReturn = DatePart("s", dtDate)
Debug.Print iReturn '= 56

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top