Choose the right date function
Access provides built-in functions you can use in expressions to format, create, parse, and calculate dates and times. Pick a table that matches what you want to do.
I want to…
Display a date or time in a specific format
Display dates in the format you want. The Result column assumes that the [StartDate] field contains the Date/Time value of January 11, 2012 17:30:15.
| Function | Use | Example | Result |
| All-purpose format function used to create custom Date/Time formats. | Format([StartDate],"yyyy-mm-dd") | 2012-01-11 | |
| Applies one of 5 built-in Date/Time formats to a value. | FormatDateTime([StartDate],vbLongDate) | Wednesday, January 11, 2012 | |
| Converts a number to a month name. Returns an error if you supply a number outside the range of 1-12. | MonthName(12) | December | |
| Converts a number to a weekday name. Returns an error if you supply a number outside the range of 1-7. | WeekdayName(2) | Monday |
* Not available in Access apps.
Learn more about how to format dates in desktop databases and Access apps.
Split a date or time into parts
Sometimes you just need to know one part of a Date/Time value, such as the year or the minute. These functions all return integer values that correspond to the interval you're looking for. The Result column assumes that [StartDate] contains the Date/Time value of Jan 11, 2012 17:30:15.
| Function | Use | Example | Result |
| Returns the year portion of the Date/Time value. | Year([StartDate]) | 2012 | |
| Returns the month portion of the Date/Time value (1 to 12). | Month([StartDate]) | 1 | |
| Returns the day portion of the Date/Time value (1 to 31). | Day([StartDate]) | 11 | |
| Returns the numeric day of the week (1 to 7). By default, Sunday is considered the first day of the week, but you can specify a different day as the first day. | Weekday([StartDate]) | 4 | |
| Hour* | Returns the hour portion of the Date/Time value (0 to 23). | Hour([StartDate]) | 17 |
| Returns the minutes portion of the Date/Time value (0 to 59). | Minute([StartDate]) | 30 | |
| Returns the seconds portion of the Date/Time value (0 to 59). | Second([StartDate]) | 15 | |
| A configurable version of the functions above, where you can specify which part of the Date/Time value you want. | DatePart("yyyy",[StartDate]) | 2012 |
* Not available in Access apps.
Turn numbers and text into dates and times
Access stores dates as double-precision floating point numbers. For example, January 11, 2012 5:30 PM is stored as 40919.72917. The integer portion, to the left of the decimal point, represents the date. The fractional portion, to the right of the decimal point, represents the time. These functions help you create this floating point number from other values, such as integers or text.
| Function | Use | Example | Result (How Access stores it) |
| Note: In an Access app, use the DateFromParts or DateWithTimeFromParts functions. | Builds a date value from integers you supply for the year, month, and day. | DateSerial(2012,1,11) | 40919.0 |
| Converts text to a Date/Time value. Handles both the Date and Time portion of the number. Tip: Use the BooleanIsDate function to determine if a text string can be converted to a Date/Time value. For example, IsDate("1/11/2012") returns True. | CDate("1/11/2012 17:30") | 40919.72917 | |
| Converts text to a Date/Time value, but doesn't convert the time portion. | DateValue("1/11/2012 17:30") | 40919.0 | |
| Converts text to a Date/Time value, but doesn't convert any date information. | TimeValue("1/11/2012 17:30") | 0.72917 | |
| Note: In an Access app, use the TimeFromParts or DateWithTimeFromParts functions. | Builds a time value from integers you supply for the hour, minute, and second. | TimeSerial(17,30,15) | 0.72934 |
* Not available in Access apps.
Add or subtract dates
These functions let you add or subtract intervals to dates, as well as calculate the difference between two Date/Time values.
| Function | Use | Example | Result |
| Adds a specific interval (such as 2 months or 3 hours) to a Date/Time value. To subtract, use a negative number as the second argument. | DateAdd("m", 1, "1/11/2012") | 2/11/2012 | |
| Determines the number of time intervals (such as days or minutes) between two Date/Time values. Note: Keep in mind you can also just subtract one date value from another to get the number of days between them, for example, #2/15/2012# - #2/1/2012# = 14. | DateDiff("d","1/11/2012","4/15/2012") | 95 |
See how to calculate the number of days between two dates.
Insert today's date or the current time
Use these functions to determine the current date or time. The Result column assumes that the current system date/time is Jan 11, 2012 17:30:15.
| Function | Use | Example | Result |
| Returns the current system date and time. | Now() | 1/11/2012 5:30:15 PM | |
| Note: In an Access app, use the Today function. | Returns the current system date. | Date() | 1/11/2012 |
| Time* | Returns the current system time. | Time() | 5:30:15 PM |
| Returns the number of seconds elapsed since midnight. | Timer() | 63015 |
* Not available in Access apps.
No comments:
Post a Comment