<%dim crumb,crumb2 crumb2="Date Time SQL Example" crumb1="Date Queries Tutorial
Access Time Example" crumb="Date/Time Query" %> DateTime Function in Access
Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
<% response.write crumb2 %>
<% response.write crumb1 %>
Home > Tutorials > Query Tutorials > <% response.write crumb %>

Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function


MultiSelect Parameter Forms Access Tutorial

Access DateTime Query

SQL DateTime query is the topic of this discussion. Many types of data change over time some examples include work pay, hourly consulting rates, part costs, etc. To select the data base on Date or Time requires knowledge of the SQL DateTime query type. This is an example of calculating worker pay as their pay rate changes over datetime.  You can see below there are two tables.

  • The M_Employees table contains the hours worked for each datetime. 

  • CLICK FOR Free Discount Coupons

    The M_Employee_PayRate table contains the worker's salary history over time.

In the image below, queries 1 and 2 are the same query but I made a copy so I could show one in design view and the other after it runs.

The key to this Access datetime query is to have accurate pay rates defined by the Start and Stop dates.  Note that the current pay rate does not have a 'Stop_Date'.  The query substitutes today's date for the null stop date.

Below is a more readable version of the SQL Select statement in our Access DateTime Function example:

Select M_Employees.Employee_ID, M_Employees.Work_Hours, M_Employees.Work_Date, M_Employees.Work_Hours*M_Employees_PayRate.PayRate as Pay From M_Employees Inner Join M_Empoyee_Payrate on M_Employees.Employee_ID=M_Employee_PayRate.Employee_ID Where (((M_Employees.Work_Date)<>=M_Employee_PayRate.Start_Date and (M_Employees.Word_Date)<=IIF(IsNull(M_Employee_PayRate.Stop_date),Date(),M_employee_payrate.stop_date)));

More Date/Time Criteria Query Examples:

When using hard coded date values in an SQL query you'll need to bracket the date value with # signs.  Here is an example:

SELECT Client_Account_Transactions.Client_Account_Trans_ID, Client_Account_Transactions.[Date Invoiced],
Client_Account_Transactions.[Invoiced Amount]
FROM Client_Account_Transactions
WHERE (((Client_Account_Transactions.[Date Invoiced])>#1/1/2006#));


Below is an example of how the date criteria looks in the design grid:


Here is an example of referencing a form field for the date critiera:

datetime criteria

To use any part of a date other than the whole date in a datetime query you'll need to know the SQL DatePart Function.

DatePart Function:  Often you'll want to break a date down into days, week, months, quarters, etc.  Below is a summary of the DatePart function within Access SQL:

The syntax for the datepart function is:

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

Arguments are as follows:
interval - Required. String expression interval of time/date you want to retrieve.
date - Required. Variant (Date) value in any standard date format.
firstdayofweek - Optional. A constant that specifies the first day of the week. The default value is Sunday.
firstweekofyear - Optional. A constant that specifies the first week of the year. The default is the 1st week in which January 1 occurs.

The interval argument has these settings:



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

More Examples of DatePart Operations:

Age Calculation DateTime Query Using Date Part Function

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365

Contact Information
<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials