Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
Access Functions
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

Access: DateDiff Function

Print

In Access, the DateDiff function returns the number of time intervals between two dates.

The syntax for the DateDiff function is:

DateDiff ( interval, date1, date2[, firstdayofweek[, firstweekofyear]] )

You can use the DateDiff function to determine how many time intervals exist between two dates. For example, you can use DateDiff to calculate the number of days between an order date and its shipdate or the number of weeks between today and the end of the year.  

interval is the interval of time to use to calculate the difference between date1 and date2. The following is a list of valid  interval values.

Interval Description
yyyy Year
q Quarter
m Month
y Day of year
w Weekday
ww Week
h Hour
n Minute
s Second

date1, date 2 are the two dates to calculate you want to use in the calculation.

firstdayofweek is optional argument that specifies the first day of the week. If not specified, Sunday is assumed. If not specified, the first week is assumed to be the week in which Jan 1 occurs. The following is a list of valid parameters for firstweekofyear.

Constant Value Description
vbUseSystem 0 Use the NLS API settings
vbSunday 1 Sunday (default used)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

firstweekofyear is optional argument that specifies the first week of the year. The following is a list of valid parameters for firstweekday.

Constant Value Description
vbUseSystem 0 Use the NLS API settings
vbFirstJan1 1 Start with the first week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week in the year that has at least 4 days
vbFirstFullWeek 3 Start  with the first full week of the year

Examples

DateDiff ('m', #12/17/2003#, #01/22/2004# )           returns 1
DateDiff ('yyyy', #11/05/2001#, #11/10/2003# )        returns 2

VBA Code

Dim MyNumber As Integer 
MyNumber = DateDiff('d',#10/06/1998#,#12/09/1998#)

This example uses the DateDiff function to display the number of days between a given date and today. Now the MyNumber variable would contain the value of 64.

SQL query

You can also use the DateDiff function in a query.

SELECT DateDiff('d',#05/05/1985#, Now()) AS Expr1
FROM Orders


If you'd like to see how it works, enter SQL statement and press Execute
 




Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2014             www.WebCheatSheet.com All Rights Reserved.