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:

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

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:

More Examples of DatePart Operations:

Age Calculation DateTime Query Using Date Part Function