Home  Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles  Search  Contact  Privacy  Links
Date Time SQL Example   Date Queries Tutorial
Access Time Example
Home > Tutorials > Query Tutorials > Date/Time Query
 





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

 

MultiSelect Parameter Forms Query Tutorial


SQL 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. 

  • 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 SQL DateTime Query 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:

datetime

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:

Setting

Description

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







A Blue Claw Database Design Template:

Asbestos Survey Project Database
 









A Blue Claw Database Design Article:

How To Create Faster Access Databases
 









Blue Claw Database Design Downloadable Tutorial:
Make Dependent Combo Box Code MS Access  Tutorial Download  








Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
 




Contact Information

Copyright 2000-2014 Blue Claw Database Design