When writing a query, you frequently need to retrieve data from a range of dates such as the last week, month, or quarter. Identifying the actual date range to use is time consuming, confusing, and is often error prone.
Note: The date range format is specific to Toad. If you want to view the syntax of the date range so you can copy it to another application , click the date range link in the Query tab, as illustrated in the following:
ScenarioYou need to create a query that retrieves a list of the orders that were placed last year. In the past, you hard coded the dates to create the following statement: With Toad, you simply select Last year to dynamically insert the correct SQL. This ensures that your query is valid regardless of the date. The following displays the revised query: The criteria is empty and contains two single quotes without a space. Toad inserts the correct SQL between these quotes when you execute the query. |
The following date range commands are available and can also be used in the Editor:
/*Today*/ |
/*Last 15 days*/ |
/*Last quarter*/ |
/*Yesterday*/ |
/*Last 30 days*/ |
/*Current year*/ |
/*Current week*/ |
/*Current month*/ |
/*Last year*/ |
/*Last week*/ |
/*Last month*/ |
|
/*Last 7 days*/ |
/*Current quarter*/ |
|
Note: These commands are case sensitive. |
To set a date range in the Query Builder
Select a column with a date data type from a table in the Diagram tab.
Select the Where Condition field below the date column and click .
Review the following for additional information:
Calendar |
Select the type of calendar to use for the date range values. If you select a calendar type other than Gregorian, you can click
Notes:
|
Date Range Values | Select a date range. |
Add additional columns and complete the query.
Tip: You can save this Query Builder file (.tsm) and click Automate to schedule query execution, generate a report of the results, and email the report to colleagues. See Automate Tasks for more information.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center