Working with Date/Time Values

Calculating with dates and times can be complicated.  Access provides a number of helpful functions. These functions often use one of several text strings for the various time intervals that we are most often interested in.

Intervals- Values you can use for interval in a function (must include the quotes):

Date Part: Pull out part of a Date/Time

General Form: DatePart(interval, date) - interval is a text string from the list above for the part of the date you want to see and date is a field name or a calculated value that results in a date/time.

There are two optional parts to this function that are not likely to use very often:
     DatePart(interval, date [,firstweekday [,firstweek)
firstweekday is the number of the day of the week that you want to use as the first day. The default is Sunday.
firstweek
is the week number that you want to use as the first week of the year. The default value is the week that contains January 1.

Examples:
DatePart("yyyy",[Birthdate]) will produce the year part of the Birthdate value.
DatePart("m",[Birthdate]) will produce the month part of the Birthdate value.
DatePart("d",Now()+30) will produce the day that is 30 days from today
DatePart("w",Now(),2) will produce the number of day of the week, starting with Monday as day 1.

DateAdd: Add to or subtract interval(s) from a Date/Time

General Form: DateAdd(interval, number, date)- Takes the date and adds the given number of intervals to it. A negative number will subtract that many intervals.

Examples:
DateAdd("m",2,[MyDate]) will add 2 months to the value in MyDate.
DateAdd("yyyy",-10,[MyDate]) will subtract 10 years from the value in MyDate.

DateDiff: Difference between two dates

General Form: DateDiff(interval, date1, date2) - Finds the number of intervals between date1 and date2. The value is positive if date2 is later than date1 and is negative date1 is the later date.

Like the DatePart fucntion, there are two optional parts to this function:
    DateDiff(interval, date1, date2 [,firstweekday [,firstweek)
firstweekday is the number of the day of the week that you want to use as the first day. The default is Sunday.
firstweek
is the week number that you want to use as the first week of the year. The default value is the week that contains January 1.

Examples:
DateDiff("yyyy", [SoldDate],Now()) will show the number of years between today and the value in SoldDate.
DateDiff("d",[PurchaseDate], [ShippedDate]) finds the number of days between the two dates.

DateValue: Turn a text string date into a number
When a field for dates is set up as text and later you want to calculate with that date, you need a function to turn the text into a number.

General Form: DateValue(date) - returns the number that represents the text string date,  like "October 14, 2000".

Examples:
DateValue("Sept. 11, 2001") returns the value 9/11/2001.
DateValue([DateGraduated]) returns the value 5/5/1999 when the field has the text value "May 5, 1999".

Format: Pattern for display a date/time
The Format function allows you to choose a standard pattern or create your own pattern for how to display a Date/Time value. The Format function can, of course, display only part of a date/time value. Don't confuse it with the DatePart function, however. The DatePart function can only pull out one part of the date/time value, like the month or day or year or hour.

General Form: Format(datetime, pattern) - takes the datetime value and formats it like pattern.

Examples:
Format(Now(),"dddd, mmm d yyyy") returns "Tuesday, Mar 7 2006" if the current date is 03/07/2006. Only the punctuation that you include in the pattern will show up.
Format(Now(),"h:m:s") return the time portion, like 13:23:45 if the current time is 1:23:45 pm.

You can also use the standard date/time formats for the pattern:

Format Pattern:

Produces:

General Date 3/8/2006 10:39:59 AM
Short Date 3/8/2006
Medium Date 08-Mar-06
Long Date Wednesday, March 08, 2006
Short Time 10:39
Medium Time 10:39 AM
Long Time 10:39:59 AM

Lessons Databases Appendix




Teachers: Request permission to use this site with your class
 
Copyright 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?


~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~


Last updated: 30 Apr 2012